<!-- build time:Tue Dec 17 2019 19:26:50 GMT+0800 (GMT+08:00) --><!doctype html><html class="theme-next mist" lang="zh-Hans"><head><meta name="generator" content="Hexo 3.8.0"><meta name="google-site-verification" content="7Tau9WyVgxnsEY9oYedu9g0U6_8akOX3wiKbaYcrg9A"><meta name="baidu-site-verification" content="EVwLiaxdxX"><link href="/css/xps13.css" rel="stylesheet" type="text/css"><link href="/css/message.css" rel="stylesheet" type="text/css"><script type="text/javascript" src="/js/jquery-1.11.3.min.js"></script><meta charset="UTF-8"><meta name="viewport" content="width=device-width,initial-scale=1,maximum-scale=1"><meta http-equiv="Cache-Control" content="no-transform"><meta http-equiv="Cache-Control" content="no-siteapp"><link href="/lib/font-awesome/css/font-awesome.min.css?v=4.6.2" rel="stylesheet" type="text/css"><link href="/css/main.css?v=5.1.1" rel="stylesheet" type="text/css"><meta name="keywords" content="DataBase,Oracle,"><link rel="alternate" href="/atom.xml" title="MrBird" type="application/atom+xml"><link rel="shortcut icon" type="image/x-icon" href="/favicon.ico?v=5.1.1"><meta name="description" content="Oracle数据类型NUMBERNUMBER表示数字类型，经常被定义成NUMBER（P，S）形式，其中：P表示数字的总位数；S表示小数点后面的位数。如：Sal NUMBER(6,2)：表示Sal列中的数据，整数位最大为4位，小数位最大位数是2位，也就是最大取值：9999.99。P不写为*号的时候代表默认38。"><meta name="keywords" content="DataBase,Oracle"><meta property="og:type" content="article"><meta property="og:title" content="Oracle basis"><meta property="og:url" content="http://mrbird.cc/Oracle-basis.html"><meta property="og:site_name" content="MrBird"><meta property="og:description" content="Oracle数据类型NUMBERNUMBER表示数字类型，经常被定义成NUMBER（P，S）形式，其中：P表示数字的总位数；S表示小数点后面的位数。如：Sal NUMBER(6,2)：表示Sal列中的数据，整数位最大为4位，小数位最大位数是2位，也就是最大取值：9999.99。P不写为*号的时候代表默认38。"><meta property="og:locale" content="zh-Hans"><meta property="og:image" content="http://mrbird.cc/img/fe7d2ccfeb41d286d5473aa20ba87622.png"><meta property="og:updated_time" content="2019-10-28T12:14:46.259Z"><meta name="twitter:card" content="summary"><meta name="twitter:title" content="Oracle basis"><meta name="twitter:description" content="Oracle数据类型NUMBERNUMBER表示数字类型，经常被定义成NUMBER（P，S）形式，其中：P表示数字的总位数；S表示小数点后面的位数。如：Sal NUMBER(6,2)：表示Sal列中的数据，整数位最大为4位，小数位最大位数是2位，也就是最大取值：9999.99。P不写为*号的时候代表默认38。"><meta name="twitter:image" content="http://mrbird.cc/img/fe7d2ccfeb41d286d5473aa20ba87622.png"><script type="text/javascript" id="hexo.configurations">var NexT=window.NexT||{},CONFIG={root:"/",scheme:"Mist",sidebar:{position:"left",display:"always",offset:12,offset_float:0,b2t:!1,scrollpercent:!1},fancybox:!1,motion:!1}</script><title>Oracle basis | MrBird</title></head><body ondragstart="return!1" class="animsition" lang="zh-Hans" style="overflow-x:hidden;padding-right:280px"><script type="text/javascript" src="/js/mo.min.js"></script><style>@media (min-width:768px) and (max-width:991px){body .header-innerr{width:700px!important}}.header-innerr{margin:0 auto;padding:100px 0 70px;width:880px}@media (min-width:1600px){.container .header-innerr{width:1200px}}.header-innerr{position:relative}.header-innerr{padding:20px 0 0}.header-innerr:after,.header-innerr:before{content:" ";display:table}.header-innerr:after{clear:both}@media (max-width:767px){.header-innerr{width:auto;padding:10px;margin-bottom:-20px}}</style><div class="container sidebar-position-left page-post-detail"><div class="headband"></div><header id="header" class="header"><div class="header-inner"><div class="site-brand-wrapper"><div class="site-meta"><link href="https://fonts.font.im/css?family=Merienda" rel="stylesheet"><div class="custom-logo-site-title"><a href="/" class="brand" rel="start"><span class="logo-line-before"><i></i></span> <span class="site-title" style="font-family:Merienda;font-size:1.3rem">MrBird</span> <span class="logo-line-after"><i></i></span></a></div><p class="site-subtitle"></p></div><div class="site-nav-toggle"><button><span class="btn-bar"></span> <span class="btn-bar"></span> <span class="btn-bar"></span></button></div></div><nav class="site-nav"><ul id="menu" class="menu"><li class="menu-item menu-item-home"><a href="/" rel="section">HOME</a></li><li class="menu-item menu-item-archives"><a href="/archives/" rel="section">ARCHIVES</a></li><li class="menu-item menu-item-tags"><a href="/tags/" rel="section">TAGS</a></li><li class="menu-item menu-item-friends"><a href="/friends/" rel="section">FRIENDS</a></li><div class="sidebar-toggle" style="display:none"><div class="sidebar-toggle-line-wrap"><span class="sidebar-toggle-line sidebar-toggle-line-first"></span> <span class="sidebar-toggle-line sidebar-toggle-line-middle"></span> <span class="sidebar-toggle-line sidebar-toggle-line-last"></span></div></div></ul><div class="site-search"><div class="popup search-popup local-search-popup"><div class="local-search-header clearfix"><span class="search-icon"><i class="fa fa-search"></i> </span><span class="popup-btn-close"><i class="fa fa-times-circle"></i></span><div class="local-search-input-wrapper"><input autocomplete="off" placeholder="Search" spellcheck="false" type="text" id="local-search-input"></div></div><div id="local-search-result"></div></div></div></nav></div><div class="header-innerr"><div class="note info" style="margin:0;letter-spacing:.15px">🐤手把手教你搭建<strong>Spring Cloud微服务权限系统</strong>（从零到部署）：<a style="color:#40dab2;font-weight:600" href="https://www.kancloud.cn/mrbird/spring-cloud" target="_blank">https://www.kancloud.cn/mrbird/spring-cloud</a></div></div></header><main id="main" class="main"><div class="main-inner"><div class="content-wrap"><div id="content" class="content"><div id="posts" class="posts-expand"><article class="post post-type-normal" itemscope itemtype="http://schema.org/Article"><link itemprop="mainEntityOfPage" href="http://mrbird.cc/Oracle-basis.html"><span hidden itemprop="author" itemscope itemtype="http://schema.org/Person"><meta itemprop="name" content="MrBird"><meta itemprop="description" content=""><meta itemprop="image" content="/images/blogImage.jpg"></span><span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization"><meta itemprop="name" content="MrBird"></span><header class="post-header"><h1 class="post-title" itemprop="name headline">Oracle basis</h1><div class="post-meta"><span class="post-time"><span class="post-meta-item-icon"><i class="fa fa-calendar-o"></i> </span><span class="post-meta-item-text">Posted on</span> <time title="创建于" itemprop="dateCreated datePublished" datetime="2016-03-01T10:44:10+08:00">2016-03-01 </time></span><span></span> <span class="post-meta-divider">|</span> <span class="page-pv"><i class="fa fa-laptop"></i>&nbsp;&nbsp;Visit count <span class="busuanzi-value" id="busuanzi_value_page_pv"></span></span></div></header><div class="post-body" itemprop="articleBody"><h2 id="Oracle数据类型"><a href="#Oracle数据类型" class="headerlink" title="Oracle数据类型"></a>Oracle数据类型</h2><p><strong>NUMBER</strong></p><p>NUMBER表示数字类型，经常被定义成NUMBER（P，S）形式，其中：P表示数字的总位数；S表示小数点后面的位数。</p><p>如：<code>Sal NUMBER(6,2)</code>：表示Sal列中的数据，整数位最大为4位，小数位最大位数是2位，也就是最大取值：9999.99。P不写为*号的时候代表默认38。<a id="more"></a></p><p><strong>CHAR</strong></p><p>CHAR表示固定长度的字符类型，经常被定义成CHAR（N）形式， N表示占用的字节数，N的最大取值是2000。</p><p>例如在表Emp中的Ename列的定义如下：<code>Ename CHAR(20)</code>：表示Ename列中最多可存储20个字节的字符串，并且占用的空间是固定的20个字节。</p><p><strong>VARCHAR2</strong></p><p>VARCHAR2表示变长的字符类型，定义格式是VARCHAR2（N）， N表示最多可占用的字节数，最大长度是4000字节。</p><p>例如在表Emp中的JOB列的定义如下： <code>JOB VARCHAR2(100)</code>：表示JOB列中最多可存储长度为100个字节的字符串。根据其中保存的数据长度，占用的空间是变化的，最大占用空间为100个字节。</p><p><strong>CHAR和VARCHAR2的区别</strong></p><ol><li><p>CHAR和VARCHAR2类型</p><p>CHAR和VARCHAR2类型都是用来表示字符串数据类型，用来在表中存放字符串信息， 比如姓名、职业、地址等。</p><p>CHAR存放定长字符，如果数据存不满定长长度，则补齐空格；</p><p>VARCHAR2存放变长字符，实际数据有多少长度则占用多少。</p><p>如保存字符串’HELLOWORLD’，共10个英文字母：</p><p>CHAR(100)： 10个字母，补齐90个空格，实际占用100个字节。</p><p>VARCHAR2(100) ：10个字母，实际占用10个字节。</p><p>CHAR类型浪费空间换取查询时间的缩短，VARCHAR2节省空间查询时间较CHAR类型要长。字符串按照自然顺序排序。</p></li><li><p>CHAR和VARCHAR2的存储编码</p><p>字符串在数据库中存储的默认单位是字节，也可显式指定为字符。如：</p><p><code>CHAR(10)</code>，等价于 <code>CHAR(10 BYTE)</code>。</p><p>如果指定单位为字符：<code>CHAR(10 CHAR)</code>，20个字节。</p><p><code>VARCHAR2(10)</code>， 等价于<code>VARCHAR2（10 BYTE）</code>。</p><p>指定单位为字符：<code>VARCHAR2(10 CHAR)</code>，20个字节。</p></li><li><p>CHAR和VARCHAR2的最大长度</p><p>CHAR类型的最大取值为2000字节，也就是定义为<code>CHAR（2000）</code>。其中最多保存2000个英文字符，1000个汉字（GBK）。</p><p>VARCHAR2最大取值为4000字节，也就是<code>VARCHAR2（4000）</code>，最多保存4000个英文字符，2000个汉字（GBK）。</p><p>CHAR如果不指定长度，默认为1个字节， VARCHAR2必须指定长度。</p></li></ol><p><strong>DATE</strong></p><p>DATE用于定义日期时间的数据，长度是7个字节，默认格式是：DD-MON-RR， 例如：“11-APR-71”。如果是中文环境，是“11-4月-71”这种形式。</p><p>例如在表Emp中的Hiredate列的定义如下：<code>Hiredate DATE</code>：表示Hiredate列中存放的是日期数据。</p><p><strong>LONG和CLOB类型</strong></p><p>LONG类型可以认为是VARCHAR2的加长版，用来存储变长字符串，最多达2GB的字符串数据，但是LONG类型有诸多限制，所以不建议使用：</p><ul><li><p>每个表只能有一个LONG类型列；</p></li><li><p>不能作为主键；</p></li><li><p>不能建立索引；</p></li><li><p>不能出现在查询条件中等</p></li></ul><p>CLOB用来存储定长或变长字符串，最多达4GB的字符串数据，ORACLE建议开发中使用CLOB替代LONG类型。</p><h2 id="SQL分类"><a href="#SQL分类" class="headerlink" title="SQL分类"></a>SQL分类</h2><p>SQL（Structured Query Language）是结构化查询语言的缩写。可分为：</p><p><strong>数据定义语言（DDL Data Definition Language）：</strong>用于建立，修改和删除数据库对象。包含：</p><ul><li><p>CREATE：创建表或其他对象的结构。</p></li><li><p>ALTER: 修改表或其他对象的结构。</p></li><li><p>DROP：删除表或其他对象的结构。</p></li><li><p>TRUNCATE：删除表数据，保留表结构。</p></li></ul><p>CREATE语句创建表格：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; create table employee (</span><br><span class="line">  2  id NUMBER(10) not null,</span><br><span class="line">  3  name VARCHAR2(20),</span><br><span class="line">  4  gender CHAR(1),</span><br><span class="line">  5  birth DATE,</span><br><span class="line">  6  salary NUMBER(6,2),</span><br><span class="line">  7  job VARCHAR2(30),</span><br><span class="line">  8  deptno NUMBER(2)</span><br><span class="line">  9  );</span><br><span class="line"></span><br><span class="line">表已创建。</span><br><span class="line"></span><br><span class="line">SQL&gt; desc employee;</span><br><span class="line"> 名称                                      是否为空? 类型</span><br><span class="line"> <span class="comment">----------------------------------------- -------- ----------------------------</span></span><br><span class="line"> ID                                        NOT NULL NUMBER(10)</span><br><span class="line"> NAME                                               VARCHAR2(20)</span><br><span class="line"> GENDER                                             CHAR(1)</span><br><span class="line"> BIRTH                                              DATE</span><br><span class="line"> SALARY                                             NUMBER(6,2)</span><br><span class="line"> JOB                                                VARCHAR2(30)</span><br><span class="line"> DEPTNO                                             NUMBER(2)</span><br></pre></td></tr></table></figure><p></p><p>修改表名：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; RENAME employee TO employee1;</span><br><span class="line"></span><br><span class="line">表已重命名。</span><br></pre></td></tr></table></figure><p></p><p>增加列：</p><p>在建表之后，要给表增加列可以使用<code>ALTER TABLE</code>的<code>ADD</code>子句实现。如在employee1表最后面增加一列：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; ALTER TABLE employee1 ADD(hiredate DATE DEFAULT SYSDATE);</span><br><span class="line"></span><br><span class="line">表已更改。</span><br><span class="line"></span><br><span class="line">SQL&gt; desc employee1</span><br><span class="line"> 名称                                      是否为空? 类型</span><br><span class="line"> <span class="comment">----------------------------------------- -------- ----------------------------</span></span><br><span class="line"> ID                                        NOT NULL NUMBER(10)</span><br><span class="line"> NAME                                               VARCHAR2(20)</span><br><span class="line"> GENDER                                             CHAR(1)</span><br><span class="line"> BIRTH                                              DATE</span><br><span class="line"> SALARY                                             NUMBER(6,2)</span><br><span class="line"> JOB                                                VARCHAR2(30)</span><br><span class="line"> DEPTNO                                             NUMBER(2)</span><br><span class="line"> HIREDATE                                           DATE</span><br></pre></td></tr></table></figure><p></p><p>删除列：</p><p>删除employee1表中的hiredate列：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; ALTER TABLE employee1 DROP(hiredate);</span><br><span class="line"></span><br><span class="line">表已更改。</span><br></pre></td></tr></table></figure><p></p><p>删除所有表数据，保留结构：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; truncate TABLE employee1;</span><br><span class="line"></span><br><span class="line">表被截断。</span><br></pre></td></tr></table></figure><p></p><p><code>TRUNCATE TABLE</code>在功能上与不带<code>WHERE</code>子句的<code>DELETE</code>语句相同：二者均删除表中的全部行。但<code>TRUNCATE TABLE</code>比<code>DELETE</code>速度快，且使用的系统和事务日志资源少。<code>DELETE</code>语句每次删除一行，并在事务日志中为所删除的每行记录一项。</p><p>修改列：</p><p>建表之后，可以改变表中列的数据类型、长度和默认值，注意这种修改仅对以后插入的数据有效，另外如果表中已经有数据的情况下，把长度由大改小，有可能不成功，比如原来的类型是<code>VARCHAR2(100)</code>，其中已经存放了100个字节长度的数据，如果要改为<code>VARCHAR2(80)</code>，则不会修改成功。</p><p>修改表employee1的列job，并增加默认值的设置：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; ALTER TABLE employee1 MODIFY(job VARCHAR2(40) DEFAULT 'manager');</span><br><span class="line"></span><br><span class="line">表已更改。</span><br></pre></td></tr></table></figure><p></p><p>删除表：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; drop table employee1;</span><br><span class="line"></span><br><span class="line">表已删除。</span><br><span class="line"></span><br><span class="line">SQL&gt; desc employee1;</span><br><span class="line">ERROR:</span><br><span class="line">ORA-04043: 对象 employee1 不存在</span><br></pre></td></tr></table></figure><p></p><p><strong>数据操纵语言（DML Data Manipulation Language）：</strong>用于改变数据表中的数据，和事务相关，执行完后需要通过事务控制语句提交后才真正的将改变应用到数据库中。</p><p>包括：</p><ul><li><p>INSERT：将数据插入到数据表中。</p></li><li><p>UPDATE：更新数据表中已存在的数据。</p></li><li><p>DELETE：删除数据表中的数据。</p></li></ul><p><strong>事务控制语言（TCL Transaction Control Language）：</strong>用来维护数据一致性的语句。</p><p>包括：</p><ul><li><p>COMMIT：提交，确认已经进行的数据改变。</p></li><li><p>ROLLBACK：回滚，取消已经进行的数据改变。</p></li><li><p>SAVEPOINT：保存点，使当前的事务可以回退到指定的保存点，便于取消部分改变。</p></li></ul><p><strong>数据查询语言（DQL Data Query Language）：</strong>用来查询需要的数据。SELECT语句。</p><p><strong>数据控制语言（DCL Data Control Language）：</strong>用于执行权限的授予与回收工作。</p><p>包括：</p><ul><li><p>GRANT：授予，用于给用户或角色授予权限。</p></li><li><p>REVOKE：用于收回用户或角色的权限。</p></li><li><p>CREATE USER：创建用户。</p></li></ul><h2 id="SQL基础查询"><a href="#SQL基础查询" class="headerlink" title="SQL基础查询"></a>SQL基础查询</h2><p><strong>FROM子句</strong></p><p>SELECT用于指定要查询的列，FROM指定要从哪个表中查询。如果要查询所有列，可以在SELECT后面使用*号，如果只查询特定的列，可以直接在SELECT后面指定列名，列名之间用逗号隔开。</p><p><strong>列的别名</strong></p><p>当我们查询的内容不是一个单纯的列，可能是一个函数，或者表达式，那么在结果集中该字段对应的字段名就是这个函数或者表达式。这样不够清晰，为此我们可以单独指定别名，这样在结果集中该字段的名字就是这个别名。若想显示自己想要的内容，使用双引号。其中”AS”可以省略。</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; SELECT ename,sal*12 as "年薪",job from emp;</span><br><span class="line"></span><br><span class="line">ENAME            年薪 JOB</span><br><span class="line"><span class="comment">---------- ---------- ---------</span></span><br><span class="line">SMITH            9600 CLERK</span><br><span class="line">ALLEN           19200 SALESMAN</span><br><span class="line">WARD            15000 SALESMAN</span><br><span class="line">JONES           35700 MANAGER</span><br><span class="line">MARTIN          15000 SALESMAN</span><br><span class="line">BLAKE           34200 MANAGER</span><br><span class="line">CLARK           29400 MANAGER</span><br><span class="line">SCOTT           36000 ANALYST</span><br><span class="line">KING            60000 PRESIDENT</span><br><span class="line">TURNER          18000 SALESMAN</span><br><span class="line">ADAMS           13200 CLERK</span><br></pre></td></tr></table></figure><p></p><p><strong>AND,OR</strong></p><p>与和或。AND优先级高于OR，可以用括号提高优先级。</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select ename,sal,job from emp where sal &gt; 1000 or job = 'CLERK';</span><br><span class="line"></span><br><span class="line">ENAME             SAL JOB</span><br><span class="line"><span class="comment">---------- ---------- ---------</span></span><br><span class="line">SMITH             800 CLERK</span><br><span class="line">ALLEN            1600 SALESMAN</span><br><span class="line">WARD             1250 SALESMAN</span><br><span class="line">JONES            2975 MANAGER</span><br><span class="line">MARTIN           1250 SALESMAN</span><br><span class="line">BLAKE            2850 MANAGER</span><br><span class="line">CLARK            2450 MANAGER</span><br><span class="line">SCOTT            3000 ANALYST</span><br><span class="line">KING             5000 PRESIDENT</span><br><span class="line">TURNER           1500 SALESMAN</span><br><span class="line">ADAMS            1100 CLERK</span><br><span class="line">JAMES             950 CLERK</span><br><span class="line">FORD             3000 ANALYST</span><br><span class="line">MILLER           1300 CLERK</span><br></pre></td></tr></table></figure><p></p><p><strong>LIKE</strong></p><p>用于模糊查询，支持两个通配符，%：表示0到多个字符，_表示一个字符。</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select ename,sal,job from emp where ename like '_L%';</span><br><span class="line"></span><br><span class="line">ENAME             SAL JOB</span><br><span class="line"><span class="comment">---------- ---------- ---------</span></span><br><span class="line">ALLEN            1600 SALESMAN</span><br><span class="line">BLAKE            2850 MANAGER</span><br><span class="line">CLARK            2450 MANAGER</span><br></pre></td></tr></table></figure><p></p><p><strong>IN,NOT IN</strong></p><p>在WHERE子句中可以用比较操作符<code>IN(list)</code>来取出符合列表范围中的数据。其中的参数list表示值列表，当列或表达式匹配于列表中的任何一个值时，条件为TRUE，该条记录则被显示出来。IN也可以理解为一个范围比较操作符，只不过这个范围是一个指定的值列表，<code>NOT IN(list)</code>取出不符合此列表中的数据记录。</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select ename,sal,job from emp where job in('CLERK','SALESMAN');</span><br><span class="line"></span><br><span class="line">ENAME             SAL JOB</span><br><span class="line"><span class="comment">---------- ---------- ---------</span></span><br><span class="line">SMITH             800 CLERK</span><br><span class="line">ALLEN            1600 SALESMAN</span><br><span class="line">WARD             1250 SALESMAN</span><br><span class="line">MARTIN           1250 SALESMAN</span><br><span class="line">TURNER           1500 SALESMAN</span><br><span class="line">ADAMS            1100 CLERK</span><br><span class="line">JAMES             950 CLERK</span><br><span class="line">MILLER           1300 CLERK</span><br></pre></td></tr></table></figure><p></p><p><strong>BETWEEN AND</strong></p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select ename,sal,job from emp where sal between 1500 and 3000;</span><br><span class="line"></span><br><span class="line">ENAME             SAL JOB</span><br><span class="line"><span class="comment">---------- ---------- ---------</span></span><br><span class="line">ALLEN            1600 SALESMAN</span><br><span class="line">JONES            2975 MANAGER</span><br><span class="line">BLAKE            2850 MANAGER</span><br><span class="line">CLARK            2450 MANAGER</span><br><span class="line">SCOTT            3000 ANALYST</span><br><span class="line">TURNER           1500 SALESMAN</span><br><span class="line">FORD             3000 ANALYST</span><br></pre></td></tr></table></figure><p></p><p><strong>ANY,ALL</strong></p><p>当我们需要判断内容&gt;，&gt;=，&lt;，&lt;=一个列表中的多个值时，需要结合ANY或ALL来使用。ANY(LIST)：大于列表中其中之一即可，即大于最小的；ALL(LIST)：大于列表中所有，即大于最大的。</p><p>列表中的内容通常不是固定值，而是一个查询结果集，所以常在子查询中，与IN道理一样。如查询谁的薪水比FORD高？如果有多个同名，比任何一个叫FORD的人高就行：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select ename from emp where sal &gt; any(select sal from emp where ename = 'FORD');</span><br><span class="line"></span><br><span class="line">ENAME</span><br><span class="line"><span class="comment">----------</span></span><br><span class="line">KING</span><br></pre></td></tr></table></figure><p></p><p><strong>查询条件中使用表达式和函数</strong></p><p>当查询需要对选出的字段进行进一步计算，可以在数字列上使用算术表达式(+、-、*、/)。表达式符合四则运算的默认优先级，如果要改变优先级可以使用括号。算术运算主要是针对数字类型的数据，对日期类型的数据可以做加减操作，表示在一个日期值上加或减一个天数。</p><div class="note danger"><p>查询条件中不能使用聚合函数！</p></div><p>查询条件中使用算数表达式，查询年薪大于5w元的员工记录：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select ename,sal,job from emp where sal*12 &gt; 50000;</span><br><span class="line"></span><br><span class="line">ENAME             SAL JOB</span><br><span class="line"><span class="comment">---------- ---------- ---------</span></span><br><span class="line">KING             5000 PRESIDENT</span><br></pre></td></tr></table></figure><p></p><p><strong>ORDER BY</strong></p><p>使用ORDER BY字句：用于对结果即按照指定的字段的值升序或者降序进行排序。ASC：升序，默认也是ASC；DESC：降序。</p><p>查看工资排名：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select ename,sal,job from emp order by sal;</span><br><span class="line"></span><br><span class="line">ENAME             SAL JOB</span><br><span class="line"><span class="comment">---------- ---------- ---------</span></span><br><span class="line">SMITH             800 CLERK</span><br><span class="line">JAMES             950 CLERK</span><br><span class="line">ADAMS            1100 CLERK</span><br><span class="line">WARD             1250 SALESMAN</span><br><span class="line">MARTIN           1250 SALESMAN</span><br><span class="line">MILLER           1300 CLERK</span><br><span class="line">TURNER           1500 SALESMAN</span><br><span class="line">ALLEN            1600 SALESMAN</span><br><span class="line">CLARK            2450 MANAGER</span><br><span class="line">BLAKE            2850 MANAGER</span><br><span class="line">JONES            2975 MANAGER</span><br><span class="line">SCOTT            3000 ANALYST</span><br><span class="line">FORD             3000 ANALYST</span><br><span class="line">KING             5000 PRESIDENT</span><br></pre></td></tr></table></figure><p></p><p>若排序的字段中有NULL值，NULL被视为最大值。当多个字段进行排序时， 每个字段可以分别指定升降序，并且排序顺序按照第一个字段优先排序，只有第一个字段值相同时才按照第二个字段排序，以此类推。</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select ename,sal,job,deptno from emp</span><br><span class="line">  2  order by deptno desc,sal desc;</span><br><span class="line"></span><br><span class="line">ENAME             SAL JOB           DEPTNO</span><br><span class="line"><span class="comment">---------- ---------- --------- ----------</span></span><br><span class="line">BLAKE            2850 MANAGER           30</span><br><span class="line">ALLEN            1600 SALESMAN          30</span><br><span class="line">TURNER           1500 SALESMAN          30</span><br><span class="line">WARD             1250 SALESMAN          30</span><br><span class="line">MARTIN           1250 SALESMAN          30</span><br><span class="line">JAMES             950 CLERK             30</span><br><span class="line">FORD             3000 ANALYST           20</span><br><span class="line">SCOTT            3000 ANALYST           20</span><br><span class="line">JONES            2975 MANAGER           20</span><br><span class="line">ADAMS            1100 CLERK             20</span><br><span class="line">SMITH             800 CLERK             20</span><br><span class="line">KING             5000 PRESIDENT         10</span><br><span class="line">CLARK            2450 MANAGER           10</span><br><span class="line">MILLER           1300 CLERK             10</span><br></pre></td></tr></table></figure><p></p><p><strong>聚合函数（分组函数，组函数）</strong></p><p>查询时需要做一些数据统计，比如：查询职员表中各部门职员的平均薪水，各部门的员工人数。当需要统计的数据并不能在职员表里直观列出，而是需要根据现有的数据计算得到结果，这种功能可以使用聚合函数来实现，即：将表的全部数据划分为几组数据，每组数据统计出一个结果。</p><p>因为是多行数据参与运算返回一行结果，也称作分组函数、多行函数、集合函数。用到的关键字：</p><ul><li><p>GOURP BY 按什么分组。</p></li><li><p>HAVING 进一步限制分组结果。</p></li></ul><p>聚合函数是忽略NULL值的。</p><p>1、MAX和MIN</p><p>用来取得列或表达式的最大、最小值，可以用来统计任何数据类型，包括数字、字符和日期。计算最早和最晚的入职时间，参数是日期：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select max(hiredate),min(hiredate) from emp;</span><br><span class="line"></span><br><span class="line">MAX(HIREDATE)  MIN(HIREDATE)</span><br><span class="line"><span class="comment">-------------- --------------</span></span><br><span class="line">23-5月 -87     17-12月-80</span><br></pre></td></tr></table></figure><p></p><p>2、AVG和SUM</p><p>AVG和SUM函数用来统计列或表达式的平均值和和值，这两个函数只能操作数字类型，并忽略NULL值。统计所有员工的总工资和平均工资：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select avg(sal) avg_sal,sum(sal) sum_sal from emp;</span><br><span class="line"></span><br><span class="line">   AVG_SAL    SUM_SAL</span><br><span class="line"><span class="comment">---------- ----------</span></span><br><span class="line">2073.21429      29025</span><br></pre></td></tr></table></figure><p></p><p>3、COUNT</p><p>COUNT函数用来计算表中的记录条数，同样忽略NULL值。例如获取职员表中一共有多少名职员记录：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select count(1) from emp;</span><br><span class="line"></span><br><span class="line">  COUNT(1)</span><br><span class="line"><span class="comment">----------</span></span><br><span class="line">        14</span><br></pre></td></tr></table></figure><p></p><p><strong>分组</strong></p><p><strong>1、GROUP BY子句</strong></p><p>其是为聚合函数服务的，可以在统计数据时细化分组。他允许将某个字段值一样的记录看成一组，然后进行统计。而不是将整张表所有记录看成一组，那么每组可以出一个统计结果。</p><p>查看每个部门的最高工资，最低工资：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select deptno "部门", max(sal) "最高工资",min(sal) "最低工资"</span><br><span class="line">  2  from emp group by deptno;</span><br><span class="line"></span><br><span class="line">      部门   最高工资   最低工资</span><br><span class="line"><span class="comment">---------- ---------- ----------</span></span><br><span class="line">        30       2850        950</span><br><span class="line">        20       3000        800</span><br><span class="line">        10       5000       1300</span><br></pre></td></tr></table></figure><p></p><p>SQL语法要求：除了聚合函数，其他不在GROUP BY子句中的列名，不能出现在SELECT语句后面。</p><p>GROUP BY进行分组的字段应在整张表中有重复数据，否则分组毛有意义GROUP BY子句后面允许指定多个字段，那么是按照这些字段值的组合相同的记录看作一组。</p><p>查看每个部门每种职位的平均工资以及工资总和：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select deptno,job,avg(sal),sum(sal)</span><br><span class="line">  2  from emp group by deptno,job;</span><br><span class="line"></span><br><span class="line">    DEPTNO JOB         AVG(SAL)   SUM(SAL)</span><br><span class="line"><span class="comment">---------- --------- ---------- ----------</span></span><br><span class="line">        20 CLERK            950       1900</span><br><span class="line">        30 SALESMAN        1400       5600</span><br><span class="line">        20 MANAGER         2975       2975</span><br><span class="line">        30 CLERK            950        950</span><br><span class="line">        10 PRESIDENT       5000       5000</span><br><span class="line">        30 MANAGER         2850       2850</span><br><span class="line">        10 CLERK           1300       1300</span><br><span class="line">        10 MANAGER         2450       2450</span><br><span class="line">        20 ANALYST         3000       6000</span><br></pre></td></tr></table></figure><p></p><p><strong>2、HAVING字句</strong></p><p>HAVING 也是用于添加过滤条件的，它的过滤实际是在统计结果之后进行的，所以HAVING是为统计结果进行过滤使用的，其不能独立出现，必须跟在GROUP BY子句后面。</p><p>查看平均工资大于2000的部门：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select deptno,avg(sal) from emp</span><br><span class="line">  2  group by deptno having avg(sal) &gt; 2000;</span><br><span class="line"></span><br><span class="line">    DEPTNO   AVG(SAL)</span><br><span class="line"><span class="comment">---------- ----------</span></span><br><span class="line">        20       2175</span><br><span class="line">        10 2916.66667</span><br></pre></td></tr></table></figure><p></p><p>HAVING子句解决了WHERE子句后面不能跟聚合函数的问题：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select deptno,avg(sal) from emp where avg(sal) &gt; 2000</span><br><span class="line">  2  group by deptno;</span><br><span class="line"><span class="keyword">select</span> deptno,<span class="keyword">avg</span>(sal) <span class="keyword">from</span> emp <span class="keyword">where</span> <span class="keyword">avg</span>(sal) &gt; <span class="number">2000</span></span><br><span class="line">                                      *</span><br><span class="line">第 <span class="number">1</span> 行出现错误:</span><br><span class="line">ORA<span class="number">-00934</span>: 此处不允许使用分组函数</span><br></pre></td></tr></table></figure><p></p><p>该语句会报错，原因在于我们的过滤条件是平均工资高于2000，而WHERE的过滤时机在于：第一次从表中查询数据时进行过滤，只有满足WHERE条件的记录才会被查询出来。而判断平均工资高于2000，首先平均工资统计是建立在数据查询出来的基础上的，所以这时WHERE已经完成了过滤。改使用HVING 子句就可解决。</p><p><strong>查询语句的执行顺序</strong></p><p>当一条查询语句中包含所有的子句，执行顺序依下列子句次序：</p><ul><li><p>FROM 子句：执行顺序为从后往前、从右到左。数据量较少的表尽量放在后面。</p></li><li><p>WHERE子句：执行顺序为自下而上、从右到左。将能过滤掉最大数量记录的条件写在WHERE 子句的最右。</p></li><li><p>GROUP BY：执行顺序从左往右分组，最好在GROUP BY前使用WHERE将不需要的记录在GROUP BY之前过滤掉。</p></li><li><p>HAVING 子句：消耗资源。尽量避免使用，HAVING 会在检索出所有记录之后才对结果集进行过滤，需要排序等操作。</p></li><li><p>SELECT子句：少用*号，尽量取字段名称。ORACLE 在解析的过程中，通过查询数据字典将*号依次转换成所有的列名，消耗时间。</p></li><li><p>ORDER BY子句：执行顺序为从左到右排序，消耗资源。</p></li></ul><h2 id="SQL关联查询"><a href="#SQL关联查询" class="headerlink" title="SQL关联查询"></a>SQL关联查询</h2><p><strong>关联查询</strong></p><p>当从多张表查询数据时，我们会建立关联关系然后在张表中进行查询工作，重点就是如何指定这些表中数据的对应关系(关联关系)，N张表查询时至少要有N-1个连接条件。</p><p>查询每个员工的名字，工资，以及部门名称和所在地：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select e.ename,e.deptno,d.dname,d.loc</span><br><span class="line">  2  from emp e join dept d</span><br><span class="line">  3  on(d.deptno = e.deptno);</span><br><span class="line"></span><br><span class="line">ENAME          DEPTNO DNAME          LOC</span><br><span class="line"><span class="comment">---------- ---------- -------------- -------------</span></span><br><span class="line">CLARK              10 ACCOUNTING     NEW YORK</span><br><span class="line">KING               10 ACCOUNTING     NEW YORK</span><br><span class="line">MILLER             10 ACCOUNTING     NEW YORK</span><br><span class="line">JONES              20 RESEARCH       DALLAS</span><br><span class="line">FORD               20 RESEARCH       DALLAS</span><br><span class="line">ADAMS              20 RESEARCH       DALLAS</span><br><span class="line">SMITH              20 RESEARCH       DALLAS</span><br><span class="line">SCOTT              20 RESEARCH       DALLAS</span><br><span class="line">WARD               30 SALES          CHICAGO</span><br><span class="line">TURNER             30 SALES          CHICAGO</span><br><span class="line">ALLEN              30 SALES          CHICAGO</span><br><span class="line">JAMES              30 SALES          CHICAGO</span><br><span class="line">BLAKE              30 SALES          CHICAGO</span><br><span class="line">MARTIN             30 SALES          CHICAGO</span><br></pre></td></tr></table></figure><p></p><p>或者：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select e.ename,e.deptno,d.dname,d.loc</span><br><span class="line">  2  from emp e,dept d</span><br><span class="line">  3  where e.deptno = d.deptno;</span><br></pre></td></tr></table></figure><p></p><p><strong>内连接</strong></p><p>内连接只返回两个关联表中所有满足连接条件的记录。</p><p><strong>外连接</strong></p><p>内连接返回两个表中所有满足连接条件的数据记录，在有些情况下，需要返回那些不满足连接条件的记录，需要使用外连接，即不仅返回满足连接条件的记录，还将返回不满足连接条件的记录。</p><p>将员工SOCTT的部门号改为50：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; update emp set deptno = 50</span><br><span class="line">  2  where ename = 'SCOTT';</span><br></pre></td></tr></table></figure><p></p><p>执行下面语句：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select e.ename,d.dname</span><br><span class="line">  2  from emp e join dept d</span><br><span class="line">  3  on(e.deptno = d.deptno);</span><br><span class="line"></span><br><span class="line">ENAME      DNAME</span><br><span class="line"><span class="comment">---------- --------------</span></span><br><span class="line">CLARK      ACCOUNTING</span><br><span class="line">KING       ACCOUNTING</span><br><span class="line">MILLER     ACCOUNTING</span><br><span class="line">JONES      RESEARCH</span><br><span class="line">FORD       RESEARCH</span><br><span class="line">ADAMS      RESEARCH</span><br><span class="line">SMITH      RESEARCH</span><br><span class="line">WARD       SALES</span><br><span class="line">TURNER     SALES</span><br><span class="line">ALLEN      SALES</span><br><span class="line">JAMES      SALES</span><br><span class="line">BLAKE      SALES</span><br><span class="line">MARTIN     SALES</span><br><span class="line"></span><br><span class="line">已选择13行。</span><br></pre></td></tr></table></figure><p></p><p>会发现SCOTT员工没有被查询出来，原因是其不满足连接条件。</p><p>外连接允许我们在关联查询的时候，以一张表作为驱动表(数据要显示全)。该表的数据全部会体现再结果集中，但是来自关联表中的字段由于不满足连接条件没有对应的记录，所以全部取NULL。外连接主要解决的问题就是显示再关联查询中不满足连接条件的记录。</p><p>外连接分为：左外连接，右外连接，全外连接。</p><p>使用左外连接查询员工信息：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select e.ename,d.dname</span><br><span class="line">  2  from emp e left outer join dept d</span><br><span class="line">  3  on(e.deptno = d.deptno);</span><br><span class="line"></span><br><span class="line">ENAME      DNAME</span><br><span class="line"><span class="comment">---------- --------------</span></span><br><span class="line">MILLER     ACCOUNTING</span><br><span class="line">KING       ACCOUNTING</span><br><span class="line">CLARK      ACCOUNTING</span><br><span class="line">FORD       RESEARCH</span><br><span class="line">ADAMS      RESEARCH</span><br><span class="line">JONES      RESEARCH</span><br><span class="line">SMITH      RESEARCH</span><br><span class="line">JAMES      SALES</span><br><span class="line">TURNER     SALES</span><br><span class="line">BLAKE      SALES</span><br><span class="line">MARTIN     SALES</span><br><span class="line">WARD       SALES</span><br><span class="line">ALLEN      SALES</span><br><span class="line">SCOTT</span><br><span class="line"></span><br><span class="line">已选择14行。</span><br></pre></td></tr></table></figure><p></p><p>可以看出，虽然SCOTT不满足连接条件，但也出现在查询结果中了。</p><p><strong>自连接</strong></p><p>当前表的一条记录对应当前表的多条记录，自连接的设计是为了解决同类型数据间又存在上下级关系的树状结构的保存与关联。</p><p>查看员工的名字以及他领导的名字：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select e.ename "员工",m.ename "领导"</span><br><span class="line">  2  from emp e,emp m</span><br><span class="line">  3  where e.mgr = m.empno;</span><br><span class="line"></span><br><span class="line">员工       领导</span><br><span class="line"><span class="comment">---------- ----------</span></span><br><span class="line">FORD       JONES</span><br><span class="line">SCOTT      JONES</span><br><span class="line">TURNER     BLAKE</span><br><span class="line">ALLEN      BLAKE</span><br><span class="line">WARD       BLAKE</span><br><span class="line">JAMES      BLAKE</span><br><span class="line">MARTIN     BLAKE</span><br><span class="line">MILLER     CLARK</span><br><span class="line">ADAMS      SCOTT</span><br><span class="line">BLAKE      KING</span><br><span class="line">JONES      KING</span><br><span class="line">CLARK      KING</span><br><span class="line">SMITH      FORD</span><br></pre></td></tr></table></figure><p></p><p>若想将没有领导的人也列出来，可以将代码改为：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select e.ename "员工",m.ename "领导"</span><br><span class="line">  2  from emp e,emp m</span><br><span class="line">  3  where e.mgr = m.empno(+);</span><br><span class="line"></span><br><span class="line">员工       领导</span><br><span class="line"><span class="comment">---------- ----------</span></span><br><span class="line">FORD       JONES</span><br><span class="line">SCOTT      JONES</span><br><span class="line">JAMES      BLAKE</span><br><span class="line">TURNER     BLAKE</span><br><span class="line">MARTIN     BLAKE</span><br><span class="line">WARD       BLAKE</span><br><span class="line">ALLEN      BLAKE</span><br><span class="line">MILLER     CLARK</span><br><span class="line">ADAMS      SCOTT</span><br><span class="line">CLARK      KING</span><br><span class="line">BLAKE      KING</span><br><span class="line">JONES      KING</span><br><span class="line">SMITH      FORD</span><br><span class="line">KING</span><br><span class="line"></span><br><span class="line">已选择14行。</span><br></pre></td></tr></table></figure><p></p><p>等同于：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select e.ename "员工",m.ename "领导"</span><br><span class="line">  2  from emp e left join emp m</span><br><span class="line">  3  on(e.mgr = m.empno);</span><br><span class="line"></span><br><span class="line">员工       领导</span><br><span class="line"><span class="comment">---------- ----------</span></span><br><span class="line">FORD       JONES</span><br><span class="line">SCOTT      JONES</span><br><span class="line">JAMES      BLAKE</span><br><span class="line">TURNER     BLAKE</span><br><span class="line">MARTIN     BLAKE</span><br><span class="line">WARD       BLAKE</span><br><span class="line">ALLEN      BLAKE</span><br><span class="line">MILLER     CLARK</span><br><span class="line">ADAMS      SCOTT</span><br><span class="line">CLARK      KING</span><br><span class="line">BLAKE      KING</span><br><span class="line">JONES      KING</span><br><span class="line">SMITH      FORD</span><br><span class="line">KING</span><br><span class="line"></span><br><span class="line">已选择14行。</span><br></pre></td></tr></table></figure><p></p><p><strong>子查询</strong></p><p>子查询通常是将其查询出来的结果集提供给其他SQL语句使用，通常嵌套在实际要运行的SQL语句之中。</p><p>1、子查询在WHERE子句中。</p><p>在WHERE查询条件中的限制条件不是一个确定的值，而是来自于另外一个查询的结果。比如查看工资比CLARK高的员工信息：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select ename,job,sal from emp</span><br><span class="line">  2  where sal &gt; (select sal from emp</span><br><span class="line">  3  where ename = 'CLARK');</span><br><span class="line"></span><br><span class="line">ENAME      JOB              SAL</span><br><span class="line"><span class="comment">---------- --------- ----------</span></span><br><span class="line">JONES      MANAGER         2975</span><br><span class="line">BLAKE      MANAGER         2850</span><br><span class="line">SCOTT      ANALYST         3000</span><br><span class="line">KING       PRESIDENT       5000</span><br><span class="line">FORD       ANALYST         3000</span><br></pre></td></tr></table></figure><p></p><p>根据返回结果的不同，子查询可分为<strong>单行单列子查询</strong>、<strong>多行单列子查询</strong>及<strong>多行多列子查询</strong>。</p><p><img src="img/fe7d2ccfeb41d286d5473aa20ba87622.png" alt="fe7d2ccfeb41d286d5473aa20ba87622.png"></p><p>如果子查询返回多行，主查询中要使用多行比较操作符，包括IN、ALL、ANY。其中ALL和ANY不能单独使用，需要配合单行比较操作符&gt;、&gt;=、&lt;、&lt;= 一起使用。</p><p>例如查询出部门中有SALESMAN但职位不是SALESMAN的员工的信息：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select ename,job,sal from emp</span><br><span class="line">  2  where deptno in</span><br><span class="line">  3  (<span class="keyword">select</span> deptno <span class="keyword">from</span> emp</span><br><span class="line">  <span class="number">4</span>  <span class="keyword">where</span> job = <span class="string">'SALESMAN'</span>)</span><br><span class="line">  <span class="number">5</span>  <span class="keyword">and</span> job &lt;&gt; <span class="string">'SALESMAN'</span>;</span><br><span class="line"></span><br><span class="line">ENAME      JOB              SAL</span><br><span class="line"><span class="comment">---------- --------- ----------</span></span><br><span class="line">JAMES      CLERK            950</span><br><span class="line">BLAKE      MANAGER         2850</span><br></pre></td></tr></table></figure><p></p><p>由于子句<code>select deptno from emp where job = &#39;SALESMAN&#39;</code>查询结果为多行单列：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select deptno from emp</span><br><span class="line">  2  where job = 'SALESMAN';</span><br><span class="line"></span><br><span class="line">    DEPTNO</span><br><span class="line"><span class="comment">----------</span></span><br><span class="line">        30</span><br><span class="line">        30</span><br><span class="line">        30</span><br><span class="line">        30</span><br></pre></td></tr></table></figure><p></p><p>所以不能用=，而用IN。</p><p>查看所有比SALESMAN和CLERK职位工资都要高的员工信息：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select ename,job,sal from emp</span><br><span class="line">  2  where sal &gt; all(</span><br><span class="line">  3  <span class="keyword">select</span> sal <span class="keyword">from</span> emp</span><br><span class="line">  <span class="number">4</span>  <span class="keyword">where</span> job <span class="keyword">in</span>(<span class="string">'SALESMAN'</span>,<span class="string">'CLERK'</span>));</span><br><span class="line"></span><br><span class="line">ENAME      JOB              SAL</span><br><span class="line"><span class="comment">---------- --------- ----------</span></span><br><span class="line">CLARK      MANAGER         2450</span><br><span class="line">BLAKE      MANAGER         2850</span><br><span class="line">JONES      MANAGER         2975</span><br><span class="line">SCOTT      ANALYST         3000</span><br><span class="line">FORD       ANALYST         3000</span><br><span class="line">KING       PRESIDENT       5000</span><br></pre></td></tr></table></figure><p></p><p>EXISTS关键字:</p><p>用在过滤条件中，该关键字后面跟一个子查询只要子查询能查询至少一条数据，EXISTS就返回TRUE。</p><p>例如列出那些有员工的部门信息：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select d.deptno,d.dname from dept d</span><br><span class="line">  2  where exists(<span class="keyword">select</span> <span class="number">1</span> <span class="keyword">from</span></span><br><span class="line">  <span class="number">3</span>  emp e <span class="keyword">where</span> e.deptno = d.deptno);</span><br><span class="line"></span><br><span class="line">    DEPTNO DNAME</span><br><span class="line"><span class="comment">---------- --------------</span></span><br><span class="line">        10 ACCOUNTING</span><br><span class="line">        20 RESEARCH</span><br><span class="line">        30 SALES</span><br></pre></td></tr></table></figure><p></p><p>2、子查询在HAVING部分</p><p>子查询不仅可以出现在WHERE子句中，还可以出现在HAVING部分。</p><p>例如查询列出最低薪水高于30号部门的最低薪水的部门信息：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select deptno,min(sal) from emp</span><br><span class="line">  2  group by deptno having min(sal) &gt;</span><br><span class="line">  3  (<span class="keyword">select</span> <span class="keyword">min</span>(sal) <span class="keyword">from</span> emp</span><br><span class="line">  <span class="number">4</span>  <span class="keyword">where</span> deptno = <span class="number">30</span>);</span><br><span class="line"></span><br><span class="line">    DEPTNO   MIN(SAL)</span><br><span class="line"><span class="comment">---------- ----------</span></span><br><span class="line">        50       3000</span><br><span class="line">        10       1300</span><br></pre></td></tr></table></figure><p></p><p>3、子查询在FROM部分</p><p>在查询语句中，FROM子句用来指定要查询的表。如果要在一个子查询的结果中继续查询，则子查询出现在FROM 子句中，这个子查询也称作行内视图或者匿名视图。这时，把子查询当作视图对待，但视图没有名字，只能在当前的SQL语句中有效。</p><p>查询出薪水比本部门平均薪水高的员工信息：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select e.ename,e.job,e.sal from emp e,</span><br><span class="line">  2  (<span class="keyword">select</span> deptno,<span class="keyword">avg</span>(sal) avg_sal <span class="keyword">from</span> emp <span class="keyword">group</span> <span class="keyword">by</span> deptno) d</span><br><span class="line">  <span class="number">3</span>  <span class="keyword">where</span> e.deptno = d.deptno</span><br><span class="line">  <span class="number">4</span>  <span class="keyword">and</span> e.sal &gt; d.avg_sal</span><br><span class="line">  <span class="number">5</span>  <span class="keyword">order</span> <span class="keyword">by</span> e.deptno;</span><br><span class="line"></span><br><span class="line">ENAME      JOB              SAL</span><br><span class="line"><span class="comment">---------- --------- ----------</span></span><br><span class="line">KING       PRESIDENT       5000</span><br><span class="line">JONES      MANAGER         2975</span><br><span class="line">FORD       ANALYST         3000</span><br><span class="line">ALLEN      SALESMAN        1600</span><br><span class="line">BLAKE      MANAGER         2850</span><br></pre></td></tr></table></figure><p></p><p>4、子查询在SELECT部分</p><p>把子查询放在SELECT子句部分，可以认为是外连接的另一种表现形式，使用更灵活：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select e.ename,e.job,e.sal,</span><br><span class="line">  2  (<span class="keyword">select</span> d.deptno <span class="keyword">from</span> dept d</span><br><span class="line">  <span class="number">3</span>  <span class="keyword">where</span> d.deptno = e.deptno) deptno</span><br><span class="line">  <span class="number">4</span>  <span class="keyword">from</span> emp e;</span><br><span class="line"></span><br><span class="line">ENAME      JOB              SAL     DEPTNO</span><br><span class="line"><span class="comment">---------- --------- ---------- ----------</span></span><br><span class="line">SMITH      CLERK            800         20</span><br><span class="line">ALLEN      SALESMAN        1600         30</span><br><span class="line">WARD       SALESMAN        1250         30</span><br><span class="line">JONES      MANAGER         2975         20</span><br><span class="line">MARTIN     SALESMAN        1250         30</span><br><span class="line">BLAKE      MANAGER         2850         30</span><br><span class="line">CLARK      MANAGER         2450         10</span><br><span class="line">SCOTT      ANALYST         3000</span><br><span class="line">KING       PRESIDENT       5000         10</span><br><span class="line">TURNER     SALESMAN        1500         30</span><br><span class="line">ADAMS      CLERK           1100         20</span><br><span class="line">JAMES      CLERK            950         30</span><br><span class="line">FORD       ANALYST         3000         20</span><br><span class="line">MILLER     CLERK           1300         10</span><br></pre></td></tr></table></figure><p></p><p>可以看出，即使不满足where条件的SCOTT也被列出来了，所以，相当于外连接。</p><p>5、DDL中使用子查询：</p><p>创建表可以将一个查询的结果集创建为一张表。</p><p>创建一个10号部门员工信息表 ：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; create table emp_10</span><br><span class="line">  2  as</span><br><span class="line">  3  <span class="keyword">select</span> * <span class="keyword">from</span> emp</span><br><span class="line">  <span class="number">4</span>  <span class="keyword">where</span> deptno = <span class="number">10</span>;</span><br><span class="line"></span><br><span class="line">表已创建。</span><br><span class="line"></span><br><span class="line">SQL&gt; select * from emp_10;</span><br><span class="line"></span><br><span class="line">     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO</span><br><span class="line"><span class="comment">---------- ---------- --------- ---------- -------------- ---------- ---------- ----------</span></span><br><span class="line">      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10</span><br><span class="line">      7839 KING       PRESIDENT            17-11月-81           5000                    10</span><br><span class="line">      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10</span><br></pre></td></tr></table></figure><p></p><h2 id="分页查询"><a href="#分页查询" class="headerlink" title="分页查询"></a>分页查询</h2><p>分页是将查询的结果集分批显示目的视为了解决时间，性能，资源消耗，和用户需求。当查询结果集条目数非常多时。通常会使用分页。分页在标准SQL中没有定义，所以不同的数据库管理系统对于分页的语句也是不一样的。</p><p>ROWNUM：ROWNUM被称作伪列，用于返回标识行数据顺序的数字。该关键字不是所有数据库都有的。</p><p>ROWNUM在SELECT被当作一个字段去使用，他不是表中真实的字段，当我们从表中查询出一条数据后，该字段就会为这一条记录编一个行号，从1开始，自动递增。ROWNUM默认值为1。在使用ROWNUM对结果集进行编号时不要使用ROWNUM做&gt;1以上的数字的判断，否则该结果集将得不到任何记录。除非ROWNUM从1开始(包含1)。</p><p>例如：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line"></span><br><span class="line">    ROWNUM ENAME      JOB</span><br><span class="line"><span class="comment">---------- ---------- ---------</span></span><br><span class="line">         1 SMITH      CLERK</span><br><span class="line">         2 ALLEN      SALESMAN</span><br><span class="line">         3 WARD       SALESMAN</span><br><span class="line">         4 JONES      MANAGER</span><br><span class="line">         5 MARTIN     SALESMAN</span><br><span class="line">         6 BLAKE      MANAGER</span><br><span class="line">         7 CLARK      MANAGER</span><br><span class="line">         8 SCOTT      ANALYST</span><br><span class="line">         9 KING       PRESIDENT</span><br><span class="line">        10 TURNER     SALESMAN</span><br><span class="line">        11 ADAMS      CLERK</span><br><span class="line">        12 JAMES      CLERK</span><br><span class="line">        13 FORD       ANALYST</span><br><span class="line">        14 MILLER     CLERK</span><br></pre></td></tr></table></figure><p></p><p>ROWNUM只能从1计数，不能从结果集中直接截取。下面的查询语句将没有结果：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select rownum,ename,job from emp</span><br><span class="line">  2  where rownum between 6 and 10;</span><br><span class="line"></span><br><span class="line">未选定行</span><br></pre></td></tr></table></figure><p></p><p>注意区分下面语句，此处ROWNUM可以不从1开始，因为他已经生成一张表了：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select * from</span><br><span class="line">  2  (<span class="keyword">select</span> <span class="keyword">rownum</span> rn,ename,job <span class="keyword">from</span> emp)</span><br><span class="line">  <span class="number">3</span>  <span class="keyword">where</span> rn <span class="keyword">between</span> <span class="number">6</span> <span class="keyword">and</span> <span class="number">10</span>;</span><br><span class="line"></span><br><span class="line">        RN ENAME      JOB</span><br><span class="line"><span class="comment">---------- ---------- ---------</span></span><br><span class="line">         6 BLAKE      MANAGER</span><br><span class="line">         7 CLARK      MANAGER</span><br><span class="line">         8 SCOTT      ANALYST</span><br><span class="line">         9 KING       PRESIDENT</span><br><span class="line">        10 TURNER     SALESMAN</span><br></pre></td></tr></table></figure><p></p><p>按照工资从高到低排序后，取6到10名：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select * from</span><br><span class="line">  2  (<span class="keyword">select</span> <span class="keyword">rownum</span> rn,ename,job,sal <span class="keyword">from</span> emp</span><br><span class="line">  <span class="number">3</span>  <span class="keyword">order</span> <span class="keyword">by</span> sal <span class="keyword">desc</span>) t</span><br><span class="line">  <span class="number">4</span>  <span class="keyword">where</span> t.rn <span class="keyword">between</span> <span class="number">6</span> <span class="keyword">and</span> <span class="number">10</span>;</span><br><span class="line"></span><br><span class="line">        RN ENAME      JOB              SAL</span><br><span class="line"><span class="comment">---------- ---------- --------- ----------</span></span><br><span class="line">         9 KING       PRESIDENT       5000</span><br><span class="line">         8 SCOTT      ANALYST         3000</span><br><span class="line">         6 BLAKE      MANAGER         2850</span><br><span class="line">         7 CLARK      MANAGER         2450</span><br><span class="line">        10 TURNER     SALESMAN        1500</span><br></pre></td></tr></table></figure><p></p><p>上面的写法是先编号后排序的，所以上面这种写法得出的序号是不对的。而应该先排序再编号，再根据编号取范围（嵌套两层，先排序，排序结果再编号）：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select * from</span><br><span class="line">  2  (<span class="keyword">select</span> <span class="keyword">rownum</span> rn,ename,job,sal <span class="keyword">from</span></span><br><span class="line">  <span class="number">3</span>  (<span class="keyword">select</span> * <span class="keyword">from</span> emp <span class="keyword">order</span> <span class="keyword">by</span> sal <span class="keyword">desc</span>)) t</span><br><span class="line">  <span class="number">4</span>  <span class="keyword">where</span> t.rn <span class="keyword">between</span> <span class="number">6</span> <span class="keyword">and</span> <span class="number">10</span>;</span><br><span class="line"></span><br><span class="line">        RN ENAME      JOB              SAL</span><br><span class="line"><span class="comment">---------- ---------- --------- ----------</span></span><br><span class="line">         6 CLARK      MANAGER         2450</span><br><span class="line">         7 ALLEN      SALESMAN        1600</span><br><span class="line">         8 TURNER     SALESMAN        1500</span><br><span class="line">         9 MILLER     CLERK           1300</span><br><span class="line">        10 WARD       SALESMAN        1250</span><br></pre></td></tr></table></figure><p></p><h2 id="排序函数"><a href="#排序函数" class="headerlink" title="排序函数"></a>排序函数</h2><p>可以根据指定的字段进行分组，再根据指定的字段排序后生成一个组内编号。</p><p>1、ROW_NUMBER：生成组内<strong>连续且唯一</strong>的数字</p><p>查看公司每个部门的工资排名，按照部门分组，按照工资降序排列生成编号：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select ename,deptno,sal,</span><br><span class="line">  2  row_number() over(</span><br><span class="line">  3  partition by deptno</span><br><span class="line">  4  order by sal desc) rank from emp;</span><br><span class="line"></span><br><span class="line">ENAME          DEPTNO        SAL       RANK</span><br><span class="line"><span class="comment">---------- ---------- ---------- ----------</span></span><br><span class="line">KING               10       5000          1</span><br><span class="line">CLARK              10       2450          2</span><br><span class="line">MILLER             10       1300          3</span><br><span class="line">FORD               20       3000          1</span><br><span class="line">JONES              20       2975          2</span><br><span class="line">ADAMS              20       1100          3</span><br><span class="line">SMITH              20        800          4</span><br><span class="line">BLAKE              30       2850          1</span><br><span class="line">ALLEN              30       1600          2</span><br><span class="line">TURNER             30       1500          3</span><br><span class="line">WARD               30       1250          4</span><br><span class="line">MARTIN             30       1250          5</span><br><span class="line">JAMES              30        950          6</span><br><span class="line">SCOTT              50       3000          1</span><br></pre></td></tr></table></figure><p></p><p>2、RANK函数：生成<strong>不连续不唯一</strong>的数字排序字段相同的记录，得到的数字一样后续内容会根据重复的行数自动跳号。比如：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select ename,deptno,sal,</span><br><span class="line">  2  rank() over(</span><br><span class="line">  3  partition by deptno</span><br><span class="line">  4  order by sal desc) rank from emp;</span><br><span class="line"></span><br><span class="line">ENAME          DEPTNO        SAL       RANK</span><br><span class="line"><span class="comment">---------- ---------- ---------- ----------</span></span><br><span class="line">KING               10       5000          1</span><br><span class="line">CLARK              10       2450          2</span><br><span class="line">MILLER             10       1300          3</span><br><span class="line">FORD               20       3000          1</span><br><span class="line">JONES              20       2975          2</span><br><span class="line">ADAMS              20       1100          3</span><br><span class="line">SMITH              20        800          4</span><br><span class="line">BLAKE              30       2850          1</span><br><span class="line">ALLEN              30       1600          2</span><br><span class="line">TURNER             30       1500          3</span><br><span class="line">WARD               30       1250          4</span><br><span class="line">MARTIN             30       1250          4</span><br><span class="line">JAMES              30        950          6</span><br><span class="line">SCOTT              50       3000          1</span><br></pre></td></tr></table></figure><p></p><p>3、DENSE_RANK()：函数生成<strong>连续但不唯一</strong>的数字：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select ename,deptno,sal,</span><br><span class="line">  2  dense_rank() over(</span><br><span class="line">  3  partition by deptno</span><br><span class="line">  4  order by sal desc) rank from emp;</span><br><span class="line"></span><br><span class="line">ENAME          DEPTNO        SAL       RANK</span><br><span class="line"><span class="comment">---------- ---------- ---------- ----------</span></span><br><span class="line">KING               10       5000          1</span><br><span class="line">CLARK              10       2450          2</span><br><span class="line">MILLER             10       1300          3</span><br><span class="line">FORD               20       3000          1</span><br><span class="line">JONES              20       2975          2</span><br><span class="line">ADAMS              20       1100          3</span><br><span class="line">SMITH              20        800          4</span><br><span class="line">BLAKE              30       2850          1</span><br><span class="line">ALLEN              30       1600          2</span><br><span class="line">TURNER             30       1500          3</span><br><span class="line">WARD               30       1250          4</span><br><span class="line">MARTIN             30       1250          4</span><br><span class="line">JAMES              30        950          5</span><br><span class="line">SCOTT              50       3000          1</span><br></pre></td></tr></table></figure><p></p><h2 id="高级分组函数"><a href="#高级分组函数" class="headerlink" title="高级分组函数"></a>高级分组函数</h2><h2 id="集合操作"><a href="#集合操作" class="headerlink" title="集合操作"></a>集合操作</h2><p>1、UNION和UNION ALL</p><p>用来获取两个或两个以上结果集的并集（结果集的列必须一一对应）：</p><ul><li><p>UNION操作符会自动去掉合并后的重复记录。</p></li><li><p>UNION ALL返回两个结果集中的所有行，包括重复的行。</p></li><li><p>UNION操作符对查询结果排序，UNION ALL不排序。</p></li></ul><p>合并职位是’MANAGER’的员工和薪水大于2500的员工集合，查看两种方式的结果差别：</p><p>UNION：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select ename,job,sal from emp</span><br><span class="line">  2  where job = 'MANAGER'</span><br><span class="line">  3  UNION</span><br><span class="line">  4  <span class="keyword">select</span> ename,job,sal <span class="keyword">from</span> emp</span><br><span class="line">  <span class="number">5</span>  <span class="keyword">where</span> sal &gt; <span class="number">2500</span>;</span><br><span class="line"></span><br><span class="line">ENAME      JOB              SAL</span><br><span class="line"><span class="comment">---------- --------- ----------</span></span><br><span class="line">BLAKE      MANAGER         2850</span><br><span class="line">CLARK      MANAGER         2450</span><br><span class="line">FORD       ANALYST         3000</span><br><span class="line">JONES      MANAGER         2975</span><br><span class="line">KING       PRESIDENT       5000</span><br><span class="line">SCOTT      ANALYST         3000</span><br></pre></td></tr></table></figure><p></p><p>UNION ALL：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select ename,job,sal from emp</span><br><span class="line">  2  where job = 'MANAGER'</span><br><span class="line">  3  UNION ALL</span><br><span class="line">  4  <span class="keyword">select</span> ename,job,sal <span class="keyword">from</span> emp</span><br><span class="line">  <span class="number">5</span>  <span class="keyword">where</span> sal &gt; <span class="number">2500</span>;</span><br><span class="line"></span><br><span class="line">ENAME      JOB              SAL</span><br><span class="line"><span class="comment">---------- --------- ----------</span></span><br><span class="line">JONES      MANAGER         2975</span><br><span class="line">BLAKE      MANAGER         2850</span><br><span class="line">CLARK      MANAGER         2450</span><br><span class="line">JONES      MANAGER         2975</span><br><span class="line">BLAKE      MANAGER         2850</span><br><span class="line">SCOTT      ANALYST         3000</span><br><span class="line">KING       PRESIDENT       5000</span><br><span class="line">FORD       ANALYST         3000</span><br></pre></td></tr></table></figure><p></p><p>2、INTERSECT（[ˌɪntəˈsekt]，相交）</p><p>INTERSECT函数获得两个结果集的交集，只有同时存在于两个结果集中的数据，才被显示输出。使用INTERSECT操作符后的结果集会以第一列的数据作升序排列。</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select ename,job,sal from emp</span><br><span class="line">  2  where job = 'MANAGER'</span><br><span class="line">  3  INTERSECT</span><br><span class="line">  4  <span class="keyword">select</span> ename,job,sal <span class="keyword">from</span> emp</span><br><span class="line">  <span class="number">5</span>  <span class="keyword">where</span> sal &gt; <span class="number">2500</span>;</span><br><span class="line"></span><br><span class="line">ENAME      JOB              SAL</span><br><span class="line"><span class="comment">---------- --------- ----------</span></span><br><span class="line">BLAKE      MANAGER         2850</span><br><span class="line">JONES      MANAGER         2975</span><br></pre></td></tr></table></figure><p></p><p>3、MINUS（英[ˈmaɪnəs]，减去）</p><p>MINUS函数获取两个结果集的差集。只有在第一个结果集中存在，在第二个结果集中不存在的数据，才能够被显示出来。也就是结果集一减去结果集二的结果。</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select ename,job,sal from emp</span><br><span class="line">  2  where job = 'MANAGER'</span><br><span class="line">  3  MINUS</span><br><span class="line">  4  <span class="keyword">select</span> ename,job,sal <span class="keyword">from</span> emp</span><br><span class="line">  <span class="number">5</span>  <span class="keyword">where</span> sal &gt; <span class="number">2500</span>;</span><br><span class="line"></span><br><span class="line">ENAME      JOB              SAL</span><br><span class="line"><span class="comment">---------- --------- ----------</span></span><br><span class="line">CLARK      MANAGER         2450</span><br></pre></td></tr></table></figure><p></p><h2 id="视图"><a href="#视图" class="headerlink" title="视图"></a>视图</h2><p>视图(VIEW)也被称作虚表，即虚拟的表，是一组数据的逻辑表示，其本质是对应于一条SELECT语句，结果集被赋予一个名字，即视图名字。视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化。</p><p>作用：</p><p>1、重用子查询。SELECT语句中的FROM子句中，我们常会使用一个子查询，然后将结果当作表再进行查询工作，若很多SELECT语句中都要用到该子查询，就可以将这个子查询定义为一个试图进行重用，这样也可以简化SQL语句的复杂度。</p><p>2、限制数据访问，可以隐藏真实的表中字段信息，表的名字，字段的名字，这样可以避免访问敏感信息等。</p><p>根据视图所对应的子查询种类分为几种类型：</p><ul><li><p>SELECT语句是基于单表建立的，且不包含任何函数运算、表达式或分组函数，叫做<strong>简单视图</strong>，此时视图是基表的子集；</p></li><li><p>SELECT语句同样是基于单表，但包含了单行函数、表达式、分组函数或GROUP BY子句，叫做<strong>复杂视图</strong>；</p></li><li><p>SELECT语句是基于多个表的，叫做<strong>连接视图</strong>。</p></li></ul><p>创建一个简单视图V_EMP_10，来显示部门10中的员工的编码、姓名和薪水：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; create view v_emp_10</span><br><span class="line">  2  as</span><br><span class="line">  3  <span class="keyword">select</span> empno,ename,sal</span><br><span class="line">  <span class="number">4</span>  <span class="keyword">from</span> emp <span class="keyword">where</span> deptno = <span class="number">10</span>;</span><br><span class="line"></span><br><span class="line">视图已创建。</span><br><span class="line"></span><br><span class="line">SQL&gt; desc v_emp_10;</span><br><span class="line"> 名称                                      是否为空? 类型</span><br><span class="line"> <span class="comment">----------------------------------------- -------- ----------------------------</span></span><br><span class="line"> EMPNO                                     NOT NULL NUMBER(4)</span><br><span class="line"> ENAME                                              VARCHAR2(10)</span><br><span class="line"> SAL                                                NUMBER(7,2)</span><br></pre></td></tr></table></figure><p></p><p><strong>修改视图</strong></p><p>由于视图自身没有结构，完全取决于对应的查询语句，所以修改视图就是替换对应的查询语句。</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; create or replace view v_emp_10</span><br><span class="line">  2  as</span><br><span class="line">  3  <span class="keyword">select</span> empno,ename <span class="keyword">name</span>,sal salary,deptno</span><br><span class="line">  <span class="number">4</span>  <span class="keyword">from</span> emp <span class="keyword">where</span> deptno = <span class="number">20</span>;</span><br><span class="line"></span><br><span class="line">视图已创建。</span><br><span class="line"></span><br><span class="line">SQL&gt; desc v_emp_10;</span><br><span class="line"> 名称                                      是否为空? 类型</span><br><span class="line"> <span class="comment">----------------------------------------- -------- ----------------------------</span></span><br><span class="line"> EMPNO                                     NOT NULL NUMBER(4)</span><br><span class="line"> NAME                                               VARCHAR2(10)</span><br><span class="line"> SALARY                                             NUMBER(7,2)</span><br><span class="line"> DEPTNO                                             NUMBER(2)                                            NUMBER(2)</span><br></pre></td></tr></table></figure><p></p><p>对视图进行DML操作就是对视图数据来源的基表进行操作。只能对简单试图进行DML操作，复杂视图不允许DML操作，即视图定义中包含了函数、表达式、分组语句、DISTINCT关键字或ROWNUM伪列，不允许执行DML操作。</p><p>由于视图只能看到emp表中的三个字段，所以就算向视图中所有字段插入值，那么实际插入emp表中时，除了这几个字段外，其他视图看不见的字段全部插入字段默认值NULL。</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"> SQL&gt; insert into v_emp_10 values(7935,'KangKang',4500,20);</span><br><span class="line"></span><br><span class="line">已创建 1 行。</span><br><span class="line"></span><br><span class="line">SQL&gt; select * from emp where ename = 'KangKang';</span><br><span class="line"></span><br><span class="line">     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO</span><br><span class="line"><span class="comment">---------- ---------- --------- ---------- -------------- ---------- ---------- ----------</span></span><br><span class="line">      7935 KangKang                                             4500                    20</span><br></pre></td></tr></table></figure><p></p><p>下面这条语句，通过视图插入到EMP表中，但是因为部门号是10，而不是视图中的20，所以视图看不见，无法再对这个对象进行修改，这就对基表数据产生了污染，修改视图数据同样可能存在将视图数据修改后，导致视图无法再查看到它们。</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; insert into v_emp_10 values(7936,'Jane',4000,10);</span><br><span class="line"></span><br><span class="line">已创建 1 行。</span><br><span class="line"></span><br><span class="line">SQL&gt; select * from v_emp_10;</span><br><span class="line"></span><br><span class="line">     EMPNO NAME           SALARY     DEPTNO</span><br><span class="line"><span class="comment">---------- ---------- ---------- ----------</span></span><br><span class="line">      7935 KangKang         4500         20</span><br><span class="line">      7369 SMITH             800         20</span><br><span class="line">      7566 JONES            2975         20</span><br><span class="line">      7876 ADAMS            1100         20</span><br><span class="line">      7902 FORD             3000         20</span><br></pre></td></tr></table></figure><p></p><p><strong>创建具有CHECK OPTION约束的视图</strong></p><p>可以为视图添加CHECK OPTION选项，这样对视图进行DML操作时，视图会检查操作完毕后对该记录是否可见，可见不允许操作。</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; create or replace view v_emp_10</span><br><span class="line">  2  as</span><br><span class="line">  3  <span class="keyword">select</span> empno,ename <span class="keyword">name</span>,sal salary,deptno</span><br><span class="line">  <span class="number">4</span>  <span class="keyword">from</span> emp <span class="keyword">where</span> deptno = <span class="number">20</span></span><br><span class="line">  <span class="number">5</span>  <span class="keyword">with</span> <span class="keyword">check</span> <span class="keyword">option</span>;</span><br><span class="line"></span><br><span class="line">视图已创建。</span><br><span class="line"></span><br><span class="line">SQL&gt; insert into v_emp_10 values(7937,'Maria',3000,10);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> v_emp_10 <span class="keyword">values</span>(<span class="number">7937</span>,<span class="string">'Maria'</span>,<span class="number">3000</span>,<span class="number">10</span>)</span><br><span class="line">            *</span><br><span class="line">第 <span class="number">1</span> 行出现错误:</span><br><span class="line">ORA<span class="number">-01402</span>: 视图 <span class="keyword">WITH</span> <span class="keyword">CHECK</span> <span class="keyword">OPTION</span> <span class="keyword">where</span> 子句违规</span><br></pre></td></tr></table></figure><p></p><p><strong>创建具有READ ONLY约束的视图</strong></p><p>当视图被设置为READ ONLY后，不允许对该视图进行DML操作，其为只读的。</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; create or replace view v_emp_10</span><br><span class="line">  2  as</span><br><span class="line">  3  <span class="keyword">select</span> empno,ename <span class="keyword">name</span>,sal salary,deptno</span><br><span class="line">  <span class="number">4</span>  <span class="keyword">from</span> emp <span class="keyword">where</span> deptno = <span class="number">20</span></span><br><span class="line">  <span class="number">5</span>  <span class="keyword">with</span> <span class="keyword">read</span> <span class="keyword">only</span>;</span><br><span class="line"></span><br><span class="line">视图已创建。</span><br><span class="line"></span><br><span class="line">SQL&gt; insert into v_emp_10 values(7937,'Maria',3000,20);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> v_emp_10 <span class="keyword">values</span>(<span class="number">7937</span>,<span class="string">'Maria'</span>,<span class="number">3000</span>,<span class="number">20</span>)</span><br><span class="line">*</span><br><span class="line">第 <span class="number">1</span> 行出现错误:</span><br><span class="line">ORA<span class="number">-42399</span>: 无法对只读视图执行 DML 操作</span><br></pre></td></tr></table></figure><p></p><p><strong>复杂视图</strong></p><p>对应的SELECT语句中含有函数，表达式，分组，连接查询。</p><p>创建一个显示每个部门薪水情况的视图：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; create view v_emp_sal</span><br><span class="line">  2  as</span><br><span class="line">  3  <span class="keyword">select</span> d.deptno,d.dname,</span><br><span class="line">  <span class="number">4</span>  <span class="keyword">avg</span>(e.sal) avg_sal,</span><br><span class="line">  <span class="number">5</span>  <span class="keyword">sum</span>(e.sal) sum_sal,</span><br><span class="line">  <span class="number">6</span>  <span class="keyword">max</span>(e.sal) max_sal,</span><br><span class="line">  <span class="number">7</span>  <span class="keyword">min</span>(e.sal) min_sal</span><br><span class="line">  <span class="number">8</span>  <span class="keyword">from</span> emp e,dept d</span><br><span class="line">  <span class="number">9</span>  <span class="keyword">where</span> e.deptno = d.deptno</span><br><span class="line"> <span class="number">10</span>  <span class="keyword">group</span> <span class="keyword">by</span> d.deptno,d.dname;</span><br><span class="line"></span><br><span class="line">视图已创建。</span><br><span class="line"></span><br><span class="line">SQL&gt; select * from  v_emp_sal;</span><br><span class="line"></span><br><span class="line">    DEPTNO DNAME             AVG_SAL    SUM_SAL    MAX_SAL    MIN_SAL</span><br><span class="line"><span class="comment">---------- -------------- ---------- ---------- ---------- ----------</span></span><br><span class="line">        10 ACCOUNTING         3187.5      12750       5000       1300</span><br><span class="line">        20 RESEARCH             2475      12375       4500        800</span><br><span class="line">        30 SALES          1566.66667       9400       2850        950</span><br></pre></td></tr></table></figure><p></p><p>复杂视图不能进行DML操作。</p><p>当不再需要视图的定义，可以使用DROP VIEW语句删除视图：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; drop view v_emp_10;</span><br><span class="line"></span><br><span class="line">视图已删除。</span><br></pre></td></tr></table></figure><p></p><h2 id="序列"><a href="#序列" class="headerlink" title="序列"></a>序列</h2><p>序列是一个数据库对象作用是根据指定的规则生成一组数字，每次返回一个数字。常用于为表中的主键提供值。</p><p>主键：通常每张表的第一个字段就是主键，主键字段的值要求在整张表中不能为空，且值不能重复。目的是用于唯一标识每一个记录。</p><p>创建一个序列，起始数据是100，步进是1：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; create sequence emp_seq</span><br><span class="line">  2  <span class="keyword">start</span> <span class="keyword">with</span> <span class="number">100</span></span><br><span class="line">  <span class="number">3</span>  <span class="keyword">increment</span> <span class="keyword">by</span> <span class="number">1</span>;</span><br><span class="line"></span><br><span class="line">序列已创建。</span><br></pre></td></tr></table></figure><p></p><p>序列有两个伪列</p><ul><li><p>NEXTVAL：使序列生成一个数字，第一次使用时，返回的是START WITH指定的值。需要注意，序列不可逆，一旦获取下一个数字后，就不能得到上一个数字了。</p></li><li><p>CURRVAL：获取序列最后一次生成的数字，可以调用多次，不会造成序列生成下一个数字。CURRVAL必须要在序列创建完毕后，至少调用过一侧NEXTVAL输出生成一个数字后才可以使用。</p></li></ul><p>测试让序列生成一个数字：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select emp_seq.nextval from dual;</span><br><span class="line"></span><br><span class="line">   NEXTVAL</span><br><span class="line"><span class="comment">----------</span></span><br><span class="line">       100</span><br><span class="line"></span><br><span class="line">SQL&gt; select emp_seq.nextval from dual;</span><br><span class="line"></span><br><span class="line">   NEXTVAL</span><br><span class="line"><span class="comment">----------</span></span><br><span class="line">       101</span><br></pre></td></tr></table></figure><p></p><p>每运行一次，数字增加10。</p><p>获取序列最后生成的数字：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select emp_seq.currval from dual;</span><br><span class="line"></span><br><span class="line">   CURRVAL</span><br><span class="line"><span class="comment">----------</span></span><br><span class="line">       101</span><br></pre></td></tr></table></figure><p></p><p>删除序列的语法如下：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; drop sequence emp_seq;</span><br><span class="line"></span><br><span class="line">序列已删除。</span><br></pre></td></tr></table></figure><p></p><h2 id="索引"><a href="#索引" class="headerlink" title="索引"></a>索引</h2><p>索引也是数据库对象，用来提高检索效率，排序效率有效的使用会带来很好的效果。数据库管理系统自行维护索引的算法，我们只需要指定何时为某表的某字段添加即可。</p><p>为emp表的ENAME字段添加索引：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; create index idx_emp_ename on emp(ename);</span><br><span class="line"></span><br><span class="line">索引已创建。</span><br></pre></td></tr></table></figure><p></p><p>复合索引也叫多列索引，是基于多个列的索引。如果经常在ORDER BY子句中使用job和sal作为排序依据，可以建立复合索引：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; create index idx_emp_jobsal on emp(job,sal);</span><br><span class="line"></span><br><span class="line">索引已创建。</span><br></pre></td></tr></table></figure><p></p><p>当做下面的查询时，会自动应用索引idx_emp_jobsal：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select empno,ename,job,sal from emp</span><br><span class="line">  2  order by job,sal;</span><br></pre></td></tr></table></figure><p></p><p>删除索引：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; drop index idx_emp_jobsal;</span><br><span class="line"></span><br><span class="line">索引已删除。</span><br></pre></td></tr></table></figure><p></p><p>合理使用索引提升查询效率：</p><ol><li><p>为经常出现在WHERE子句中的列创建索引。</p></li><li><p>为经常出现在ORDER BY、DISTINCT后面的字段建立索引。如果建立的复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致。</p></li><li><p>为经常作为表的连接条件的列上创建索引。</p></li><li><p>不要在经常做DML操作的表上建立索引。</p></li><li><p>不要在小表上建立索引。</p></li><li><p>限制表上的索引数目，索引并不是越多越好。</p></li><li><p>删除很少被使用的、不合理的索引。</p></li></ol><h2 id="约束"><a href="#约束" class="headerlink" title="约束"></a>约束</h2><p>约束的类型：</p><ul><li><p>非空约束(Not Null)，简称NN。</p></li><li><p>唯一性约束(Unique)，简称UK。</p></li><li><p>主键约束(Primary Key)，简称PK。</p></li><li><p>外键约束(Foreign Key)，简称FK。</p></li><li><p>检查约束(Check)，简称CK。</p></li></ul><p><strong>非空约束</strong></p><p>建表时添加非空约束：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; create table employees(</span><br><span class="line">  2  eid number(6),</span><br><span class="line">  3  name varchar2(30) not null,</span><br><span class="line">  4  salary number(7,2),</span><br><span class="line">  5  hiredate date constraint employees_hiredate_nn not null);</span><br><span class="line"></span><br><span class="line">表已创建。</span><br><span class="line"></span><br><span class="line">SQL&gt; desc employees;</span><br><span class="line"> 名称                                      是否为空? 类型</span><br><span class="line"> <span class="comment">----------------------------------------- -------- ----------------------------</span></span><br><span class="line"> EID                                                NUMBER(6)</span><br><span class="line"> NAME                                      NOT NULL VARCHAR2(30)</span><br><span class="line"> SALARY                                             NUMBER(7,2)</span><br><span class="line"> HIREDATE                                  NOT NULL DATE</span><br></pre></td></tr></table></figure><p></p><p>取消非空约束：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"> SQL&gt; alter table employees modify(hiredate date null);</span><br><span class="line"></span><br><span class="line">表已更改。</span><br><span class="line"></span><br><span class="line">SQL&gt; desc employees;</span><br><span class="line"> 名称                                      是否为空? 类型</span><br><span class="line"> <span class="comment">----------------------------------------- -------- ----------------------------</span></span><br><span class="line"> EID                                                NUMBER(6)</span><br><span class="line"> NAME                                      NOT NULL DATE</span><br><span class="line"> SALARY                                             NUMBER(7,2)</span><br><span class="line"> HIREDATE                                           DATE</span><br></pre></td></tr></table></figure><p></p><p><strong>唯一性约束</strong></p><p>唯一性(Unique)约束条件用于保证字段或者字段的组合不出现重复值。当给表的某个列定义了唯一约束条件，该列的值不允许重复，但允许是NULL值。</p><p>建表的时候添加唯一性约束：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; create table employees1(</span><br><span class="line">  2  eid number(6) unique,</span><br><span class="line">  3  name varchar2(30),</span><br><span class="line">  4  email varchar(50),</span><br><span class="line">  5  salary number(7,2),</span><br><span class="line">  6  hiredate date,</span><br><span class="line">  7  constraint employees1_email_uk unique(email));</span><br><span class="line"></span><br><span class="line">表已创建。</span><br></pre></td></tr></table></figure><p></p><p><strong>主键约束</strong></p><p>主键(Primary Key)约束条件从功能上看相当于非空（NOT NULL）且唯一（UNIQUE）的组合。主键字段可以是单字段或多字段组合，即：在主键约束下的单字段或者多字段组合上不允许有空值，也不允许有重复值。</p><p>主键可以用来在表中唯一的确定一行数据。一个表上只允许建立一个主键，而其它约束条件则没有明确的个数限制。</p><p>主键选取的原则：</p><ul><li><p>主键应是对系统无意义的数据。</p></li><li><p>永远也不要更新主键，让主键除了唯一标识一行之外，再无其他的用途。</p></li><li><p>主键不应包含动态变化的数据，如时间戳。</p></li><li><p>主键应自动生成，不要人为干预，以免使它带有除了唯一标识一行以外的意义。</p></li><li><p>主键尽量建立在单列上。</p></li></ul><p>建表的时候添加主键约束：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; create table employees2(</span><br><span class="line">  2  eid number(6) primary key,</span><br><span class="line">  3  name varchar(30),</span><br><span class="line">  4  email varchar2(30),</span><br><span class="line">  5  salary number(7,2),</span><br><span class="line">  6  hiredate date);</span><br><span class="line"></span><br><span class="line">表已创建。</span><br><span class="line"></span><br><span class="line">SQL&gt; desc employees2;</span><br><span class="line"> 名称                                      是否为空? 类型</span><br><span class="line"> <span class="comment">----------------------------------------- -------- ----------------------------</span></span><br><span class="line"> EID                                       NOT NULL NUMBER(6)</span><br><span class="line"> NAME                                               VARCHAR2(30)</span><br><span class="line"> EMAIL                                              VARCHAR2(30)</span><br><span class="line"> SALARY                                             NUMBER(7,2)</span><br><span class="line"> HIREDATE                                           DATE</span><br></pre></td></tr></table></figure><p></p><p>建表后添加主键，首先创建一张没有主键的表：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"> SQL&gt; create table employees3(</span><br><span class="line">  2  eid number(6),</span><br><span class="line">  3  name varchar2(30),</span><br><span class="line">  4  email varchar2(50),</span><br><span class="line">  5  salary number(7,2),</span><br><span class="line">  6  hiredate date);</span><br><span class="line"></span><br><span class="line">表已创建。</span><br></pre></td></tr></table></figure><p></p><p>再添加主键：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; alter table employees3</span><br><span class="line">  2  add constraint emplpoyees3_eid_pk primary key(eid);</span><br><span class="line"></span><br><span class="line">表已更改。</span><br></pre></td></tr></table></figure><p></p><p><strong>外键约束</strong></p><p>外键约束条件定义在两个表的字段或一个表的两个字段上，用于保证相关两个字段的关系。比如emp表的deptno列参照dept表的deptno列，则dept称作主表或父表，emp表称作从表或子表。</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; create table employees4(</span><br><span class="line">  2  eid number(6),</span><br><span class="line">  3  name varchar2(30),</span><br><span class="line">  4  salary number(7,2),</span><br><span class="line">  5  deptno number(4));</span><br><span class="line"></span><br><span class="line">表已创建。</span><br><span class="line"></span><br><span class="line">SQL&gt; alter table employees4</span><br><span class="line">  2  add constraint employees4_deptno_fk</span><br><span class="line">  3  foreign key(deptno) references dept(deptno);</span><br><span class="line"></span><br><span class="line">表已更改。</span><br></pre></td></tr></table></figure><p></p><p><strong>检查约束</strong></p><p>检查(Check)约束条件用来强制在字段上的每个值都要满足Check中定义的条件。当定义了Check约束的列新增或修改数据时，数据必须符合Check约束中定义的条件。</p><p>员工的薪水必须大于2000元，增加检查约束：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; alter table employees4</span><br><span class="line">  2  add constraint employees4_salary_check</span><br><span class="line">  3  <span class="keyword">check</span> (salary &gt; <span class="number">2000</span>);</span><br><span class="line"></span><br><span class="line">表已更改。</span><br></pre></td></tr></table></figure><p></p><script>$(".post-body a").not(".thispage").addClass("ignore-href").attr("target","_blank")</script></div><div></div><div><div style="padding:10px 0;margin:20px auto;width:90%;text-align:center;color:#878787"><div>请作者喝瓶肥宅水~</div><button id="rewardButton" style="margin-top:10px" disable="enable" onclick='var e=document.getElementById("QR");"none"===e.style.display?e.style.display="block":e.style.display="none"'><span style="height:46px;width:46px;line-height:46px;border-radius:50%;color:#fe5f55;font-weight:600;background:#ffd5be;border:none;box-shadow:0 4px 8px 0 rgba(255,213,190,.4)">￥</span></button><div id="QR" style="display:none"><div id="wechat" style="display:inline-block"><img id="wechat_qr" src="/img/wechat_pay.png" alt="MrBird WeChat Pay"></div><div id="alipay" style="display:inline-block"><img id="alipay_qr" src="/img/ali_pay.png" alt="MrBird Alipay"></div></div></div><style>#QR img{width:auto;margin:.8em 1em 0 1em}</style></div><div><ul class="post-copyright"><li class="post-copyright-author"><strong>本文作者：</strong> MrBird</li><li class="post-copyright-link"><strong>本文链接：</strong> <a href="http://mrbird.cc/Oracle-basis.html" title="Oracle basis">http://mrbird.cc/Oracle-basis.html</a></li><li class="post-copyright-license"><strong>版权声明： </strong>本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/" rel="external nofollow" target="_blank">CC BY-NC-SA 4.0</a> 许可协议。转载请注明出处！</li></ul></div><footer class="post-footer"><div class="post-tags" style="margin-bottom:1.3rem"><a href="/tags/DataBase/" rel="tag"># DataBase</a> <a href="/tags/Oracle/" rel="tag"># Oracle</a></div><div class="post-nav"><div class="post-nav-next post-nav-item"><a href="/Servlet-Jsp-note.html" rel="next" title="Servlet Jsp note"><i class="fa fa-chevron-left"></i> Servlet Jsp note</a></div><span class="post-nav-divider"></span><div class="post-nav-prev post-nav-item"><a href="/Oracle-经典试题集.html" rel="prev" title="Oracle 经典试题集">Oracle 经典试题集 <i class="fa fa-chevron-right"></i></a></div></div></footer></article><hr><div id="container"></div><div class="post-spread"><div id="comment-div"></div><style>.valine .vlist{margin-bottom:3rem}.valine .vwrap .vcontrol .col.col-60{text-align:left}.valine .vlist .vcard .vhead,.valine .vlist .vcard section .vfooter{text-align:left}.valine .vlist .vcard section{padding-bottom:.5rem!important}.vname{color:#42b983!important}.valine .vinfo .col{text-align:left;margin-left:-27rem}div#comment-div{margin-bottom:-8rem}.valine .vlist .vcard .vcontent .code,.valine .vlist .vcard .vcontent code,.valine .vlist .vcard .vcontent pre{background:#fbfbfb}.valine .vlist .vcard .vcontent a{color:#42b983}.valine .vlist .vcard .vimg{border-radius:3px}.valine .vbtn{border-radius:2px;padding:.3rem 1.25rem}.valine .vbtn:active,.valine .vbtn:hover{color:#42b983;border-color:#42b983;background-color:#fff}.valine .vwrap .vheader .vinput:focus{border-bottom-color:#42b983}.valine .vlist .vcard .vcontent.expand:before{background:-webkit-gradient(linear,left top,left bottom,from(hsla(0,0%,100%,0)),to(hsla(0,0%,100%,.2)));background:linear-gradient(180deg,hsla(0,0%,100%,0),hsla(0,0%,100%,.2))}.valine .vlist .vcard .vcontent.expand:after{content:"点击展开";font-size:.4rem;text-align:right;left:-1rem;background:hsla(0,0%,100%,.2)}.valine .vlist .vcard section .vfooter .vat{color:#b3b3b3}.valine .vlist .vcard section .vfooter .vat:hover{color:#42b983}.vcontent img{margin:0}.valine .info{display:none}</style><script type="text/javascript" src="/js/av.min.js"></script><script type="text/javascript" src="/js/Valine.min.js"></script><script type="text/javascript" src="/js/activate-power-mode.js"></script><script>POWERMODE.colorful=!0,POWERMODE.shake=!1,document.body.addEventListener("input",POWERMODE),new Valine({el:"#comment-div",notify:!1,verify:!0,appId:"SMcDFP1bN1jgb9Lo8JmtICHm-gzGzoHsz",appKey:"dH4nrUrt3V5XiJiI6Qyejnbi",placeholder:"",path:window.location.pathname,avatar:"monsterid",guest_info:["nick","mail","link"]});var chicken='<a href="#"><img src="https://image.uisdc.com/wp-content/uploads/2018/06/uisdc-chat-chicken.gif" class="checken"></a>';$("#comment-div").prepend(chicken)</script></div></div><script>var $bqinline=$("img[alt='bq-inline']");$bqinline.css({width:"2.3rem",height:"2.3rem",display:"inline","vertical-align":"text-bottom"})</script></div><div class="comments" id="comments"></div></div><aside id="sidebar" class="sidebar" onselectstart="return!1"><div class="sidebar-inner"><ul class="sidebar-nav motion-element"><li class="sidebar-nav-toc sidebar-nav-active" data-target="post-toc-wrap">Contents</li><li class="sidebar-nav-overview" data-target="site-overview">Site Preview</li></ul><section class="site-overview sidebar-panel"><div class="sidebar-sticky sticky"><div itemscope itemtype="http://schema.org/Person"><div class="author__avatar"><img src="/images/blogImage.jpg" class="author__avatar" alt="MrBird" itemprop="image"></div><div class="author__content"><h3 class="author__name" itemprop="name">MrBird's Blog</h3><p class="author__bio" itemprop="description">A simple blog, code repository, just keep blogging</p></div><div class="author__urls-wrapper"><button class="btn btn--inverse">Follow</button><ul class="author__urls social-icons"><li><a href="http://map.baidu.com/?newmap=1&s=s%26wd%3D%E7%A6%8F%E5%B7%9E%E5%B8%82%26c%3D300&from=alamap&tpl=mapcity" target="_blank" itemprop="url" class="ignore-href"><i class="fa fa-fw fa-map-marker" aria-hidden="true"></i>&nbsp;&nbsp;FuZhou,CN</a></li><li><a href="https://love.mrbird.cc" target="_blank" itemprop="url" class="ignore-href"><i class="fa fa-fw fa-diamond" aria-hidden="true"></i>&nbsp;&nbsp;Love</a></li><li><a href="https://cloud.mrbird.cn" target="_blank" itemprop="url" class="ignore-href"><i class="fa fa-fw fa-chain" aria-hidden="true"></i>&nbsp;&nbsp;FEBS</a></li><li><a href="/atom.xml" target="_blank" itemprop="url" class="ignore-href"><i class="fa fa-fw fa-rss" aria-hidden="true"></i>&nbsp;&nbsp;RSS</a></li><li><a href="https://gitee.com/mrbirdd" target="_blank" itemprop="sameAs" class="ignore-href"><i class="fa fa-fw fa-codepen" aria-hidden="true"></i>&nbsp;&nbsp;Gitee</a></li><li><a href="https://github.com/wuyouzhuguli" target="_blank" itemprop="sameAs" class="ignore-href"><i class="fa fa-fw fa-github-alt" aria-hidden="true"></i>&nbsp;&nbsp;GitHub</a></li><li><a href="javascript:;" class="popup-trigger"><i class="fa fa-fw fa-search" aria-hidden="true"></i>&nbsp;&nbsp;Search</a></li></ul></div></div></div><script>var $urls=$(".author__urls").find("a");$urls.each(function(){var o=$(this);o.mouseenter(function(){o.css({color:"#414547"})}),o.mouseleave(function(){o.css({color:""})})})</script></section><section class="post-toc-wrap motion-element sidebar-panel sidebar-panel-active"><div class="post-toc"><div class="post-toc-content"><ol class="nav"><li class="nav-item nav-level-2"><a class="nav-link" href="#Oracle数据类型"><span class="nav-number">1.</span> <span class="nav-text">Oracle数据类型</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#SQL分类"><span class="nav-number">2.</span> <span class="nav-text">SQL分类</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#SQL基础查询"><span class="nav-number">3.</span> <span class="nav-text">SQL基础查询</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#SQL关联查询"><span class="nav-number">4.</span> <span class="nav-text">SQL关联查询</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#分页查询"><span class="nav-number">5.</span> <span class="nav-text">分页查询</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#排序函数"><span class="nav-number">6.</span> <span class="nav-text">排序函数</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#高级分组函数"><span class="nav-number">7.</span> <span class="nav-text">高级分组函数</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#集合操作"><span class="nav-number">8.</span> <span class="nav-text">集合操作</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#视图"><span class="nav-number">9.</span> <span class="nav-text">视图</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#序列"><span class="nav-number">10.</span> <span class="nav-text">序列</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#索引"><span class="nav-number">11.</span> <span class="nav-text">索引</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#约束"><span class="nav-number">12.</span> <span class="nav-text">约束</span></a></li></ol></div></div></section></div></aside></div></main><footer id="footer" class="footer" onselectstart="return!1"><div class="footer-inner"><div class="copyright">&copy; 2016 - <span itemprop="copyrightYear">2019</span>&nbsp;&nbsp; <span class="author" itemprop="copyrightHolder">MrBird</span>&nbsp;&nbsp;|<script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>&nbsp;&nbsp;UV&nbsp;<span class="busuanzi-value" id="busuanzi_value_site_uv" style="cursor:pointer" title="统计开始时间：2019年7月5日"></span> &nbsp;&nbsp;PV&nbsp;<span class="busuanzi-value" id="busuanzi_value_site_pv" style="cursor:pointer" title="统计开始时间：2019年7月5日"></span></div></div></footer><div class="back-to-top"><span style="font-family:'Source Sans Pro','Helvetica Neue',Arial,sans-serif;font-size:1.2em;font-weight:600">TOP</span></div></div><script type="text/javascript">"[object Function]"!==Object.prototype.toString.call(window.Promise)&&(window.Promise=null)</script><script type="text/javascript" src="/lib/jquery/index.js?v=2.1.3"></script><script type="text/javascript" src="/lib/fastclick/lib/fastclick.min.js?v=1.0.6"></script><script type="text/javascript" src="/lib/jquery_lazyload/jquery.lazyload.js?v=1.9.7"></script><script type="text/javascript" src="/lib/velocity/velocity.min.js?v=1.2.1"></script><script type="text/javascript" src="/lib/velocity/velocity.ui.min.js?v=1.2.1"></script><script type="text/javascript" src="/js/src/utils.js?v=5.1.1"></script><script type="text/javascript" src="/js/src/motion.js?v=5.1.1"></script><script type="text/javascript" src="/js/src/scrollspy.js?v=5.1.1"></script><script type="text/javascript" src="/js/src/post-details.js?v=5.1.1"></script><script type="text/javascript" src="/js/src/bootstrap.js?v=5.1.1"></script><script type="text/javascript">function proceedsearch(){$("body").append('<div class="search-popup-overlay local-search-pop-overlay"></div>').css("overflow","hidden"),$(".search-popup-overlay").click(onPopupClose),$(".popup").toggle();var t=$("#local-search-input");t.attr("autocapitalize","none"),t.attr("autocorrect","off"),t.focus()}var isfetched=!1,isXml=!0,search_path="search.xml";0===search_path.length?search_path="search.xml":search_path.endsWith("json")&&(isXml=!1);var path="/"+search_path,onPopupClose=function(t){$(".popup").hide(),$("#local-search-input").val(""),$(".search-result-list").remove(),$("#no-result").remove(),$(".local-search-pop-overlay").remove(),$("body").css("overflow","")},searchFunc=function(t,e,o){"use strict";$("body").append('<div class="search-popup-overlay local-search-pop-overlay"><div id="search-loading-icon"><i class="fa fa-spinner fa-pulse fa-2x fa-fw"></i></div></div>').css("overflow","hidden"),$("#search-loading-icon").css("margin","20% auto 0 auto").css("text-align","center"),$.ajax({url:t,dataType:isXml?"xml":"json",async:!0,success:function(t){isfetched=!0,$(".popup").detach().appendTo(".header-inner");var n=isXml?$("entry",t).map(function(){return{title:$("title",this).text(),content:$("content",this).text(),url:$("url",this).text()}}).get():t,r=document.getElementById(e),s=document.getElementById(o),a=function(){var t=r.value.trim().toLowerCase(),e=t.split(/[\s\-]+/);e.length>1&&e.push(t);var o=[];if(t.length>0&&n.forEach(function(n){function r(e,o,n,r){for(var s=r[r.length-1],a=s.position,i=s.word,l=[],h=0;a+i.length<=n&&0!=r.length;){i===t&&h++,l.push({position:a,length:i.length});var p=a+i.length;for(r.pop();0!=r.length&&(s=r[r.length-1],a=s.position,i=s.word,p>a);)r.pop()}return c+=h,{hits:l,start:o,end:n,searchTextCount:h}}function s(t,e){var o="",n=e.start;return e.hits.forEach(function(e){o+=t.substring(n,e.position);var r=e.position+e.length;o+='<b class="search-keyword">'+t.substring(e.position,r)+"</b>",n=r}),o+=t.substring(n,e.end)}var a=!1,i=0,c=0,l=n.title.trim(),h=l.toLowerCase(),p=n.content.trim().replace(/<[^>]+>/g,""),u=p.toLowerCase(),f=decodeURIComponent(n.url),d=[],g=[];if(""!=l&&(e.forEach(function(t){function e(t,e,o){var n=t.length;if(0===n)return[];var r=0,s=[],a=[];for(o||(e=e.toLowerCase(),t=t.toLowerCase());(s=e.indexOf(t,r))>-1;)a.push({position:s,word:t}),r=s+n;return a}d=d.concat(e(t,h,!1)),g=g.concat(e(t,u,!1))}),(d.length>0||g.length>0)&&(a=!0,i=d.length+g.length)),a){[d,g].forEach(function(t){t.sort(function(t,e){return e.position!==t.position?e.position-t.position:t.word.length-e.word.length})});var v=[];0!=d.length&&v.push(r(l,0,l.length,d));for(var C=[];0!=g.length;){var $=g[g.length-1],m=$.position,x=$.word,w=m-20,y=m+80;w<0&&(w=0),y<m+x.length&&(y=m+x.length),y>p.length&&(y=p.length),C.push(r(p,w,y,g))}C.sort(function(t,e){return t.searchTextCount!==e.searchTextCount?e.searchTextCount-t.searchTextCount:t.hits.length!==e.hits.length?e.hits.length-t.hits.length:t.start-e.start});var T=parseInt("1");T>=0&&(C=C.slice(0,T));var b="";b+=0!=v.length?"<li><a href='"+f+"' class='search-result-title'>"+s(l,v[0])+"</a>":"<li><a href='"+f+"' class='search-result-title'>"+l+"</a>",C.forEach(function(t){b+="<a href='"+f+'\'><p class="search-result">'+s(p,t)+"...</p></a>"}),b+="</li>",o.push({item:b,searchTextCount:c,hitCount:i,id:o.length})}}),1===e.length&&""===e[0])s.innerHTML='<div id="no-result"><i class="fa fa-search fa-5x" /></div>';else if(0===o.length)s.innerHTML='<div id="no-result"><i class="fa fa-frown-o fa-5x" /></div>';else{o.sort(function(t,e){return t.searchTextCount!==e.searchTextCount?e.searchTextCount-t.searchTextCount:t.hitCount!==e.hitCount?e.hitCount-t.hitCount:e.id-t.id});var a='<ul class="search-result-list">';o.forEach(function(t){a+=t.item}),a+="</ul>",s.innerHTML=a}};r.addEventListener("input",a),$(".local-search-pop-overlay").remove(),$("body").css("overflow",""),proceedsearch()}})};$(".popup-trigger").click(function(t){t.stopPropagation(),isfetched===!1?searchFunc(path,"local-search-input","local-search-result"):proceedsearch()}),$(".popup-btn-close").click(onPopupClose),$(".popup").click(function(t){t.stopPropagation()}),$(document).on("keyup",function(t){var e=27===t.which&&$(".search-popup").is(":visible");e&&onPopupClose()})</script></body><script>$(function(){$("a").not(".nav-link,.cloud-tie-join-count,.ignore-href,.jstree-anchor").addClass("animsition-link")});var burst1=new mojs.Burst({left:0,top:0,radius:{5:40},children:{shape:"circle",fill:["red","cyan","orange"],fillOpacity:.8,radiusX:3.5,radiusY:3.5}});document.addEventListener("click",function(a){null==a.target.href&&"footer"!=a.target.className&&"copyright"!=a.target.className&&"author__urls social-icons"!=a.target.className&&"author__avatar"!=a.target.className&&"sidebar sidebar-active"!=a.target.className&&burst1.tune({x:a.pageX,y:a.pageY}).generate().replay()})</script><script type="text/javascript" src="/js/message.js"></script></html><!-- rebuild by neat -->